Get Saturday and Sunday date to send Birthday's greeting

Question

Hi Guys I'm kind of new to the macros and how to set it up.

I'm trying to run a automatic birthday macros that send an email out to people saying a happy birthday message.

but I'm struggling with when its Monday i want it to initiate a message for the weekend birthdays as well, but only on a Monday. My Code keeps saying "Run-time error '13': type mismatch". Here is my Code please help as I've been struggling a week with it

Sub send_bday_greet2()
  Dim i As Long
  Dim vbSunday As String, vbSaturday As String

    For i = 2 To Sheets("Sheet1").Range("a1048576").End(xlUp).Row
        If Day(Now()) = Day(CDate(Sheets("Sheet1").Range("c" & i).Value)) And Month(Now()) = Month(CDate(Sheets("Sheet1").Range("c" & i).Value)) Then
            Call sending_bday_greetings_method2(Sheets("Sheet1").Range("a" & i).Value, Sheets("Sheet1").Range("b" & i).Value)

            ElseIf Day(Now(vbMonday)) = Day(CDate(Sheets("Sheet1").Range("c" & i).Value)) And Month(Now(vbSaturday)) And Month(Now(vbSunday)) = Month(CDate(Sheets("Sheet1").Range("c" & i).Value)) Then

                Call sending_bday_greetings_method2(Sheets("Sheet1").Range("a" & i).Value, Sheets("Sheet1").Range("b" & i).Value)

            End If
    Next
End Sub


Sub sending_bday_greetings_method2(nm As String, emid As String)

 Dim olApp As Outlook.Application
 Dim olMail As MailItem

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

s = "<p> <p align='left'><font size='3' face='arial' color='blue'><i> Dear " & nm & ", </p>" & vbNewLine

s = s & "<p> <p align='CENTER'><font size='3' face='arial' color='red'><i> We Wish you a very Happy Birthday! </p>" & vbNewLine

s = s & "<left><p align='CENTER'><img src=""http://simplyizzy.files.wordpress.com/2012/05/happy_birthday1.png"">" & vbNewLine

s = s & vbNewLine & "<left><p><p align='Left'><font size='3' face='arial' color='blue'><i>Regards<br>" & "Reutech Radar Systems</p>"

With olMail
    .To = emid
    .Subject = "Happy B'day!"
    .HTMLBody = s
    .Send
End With

Set olApp = Nothing
Set olMail = Nothing

End Sub

Apparently you want `ByVal nm As String, ByVal emid As String`. — GSerg, Mar 07 '17 at 08:50
In the sub send_bday_greet2() ElseIf Day(Now(vbMonday)) = Day(CDate(Sheets("Sheet1").Range("c" & i).Value)) And Month(Now(vbSaturday)) And Month(Now(vbSunday)) = Month(CDate(Sheets("Sheet1").Range("c" & i).Value)) Then — Clint Reid, Mar 07 '17 at 08:51
The sub where it sends the email works perfectly for sending birthdays on the current day. but the code for sending on saturdays and sundays bday's on mondays im having problems with — Clint Reid, Mar 07 '17 at 08:54
That too. You are not comparing `Month(Now(vbSaturday))` to anything which will give you [interesting results](http://stackoverflow.com/a/24686069/11683). You are also providing a parameter to `Now()` when it does not accept parameters. You are redefining already existing `vbSaturday` and `vbSunday` for no reason. When you fix all that, you will finally get the error about byref type mismatch I [was talking about](http://stackoverflow.com/questions/42643701/running-a-birthday-macros#comment72414641_42643701). — GSerg, Mar 07 '17 at 08:56
Nah the issue is with `Dim vbSunday As String, vbSaturday As String`! I'm posting a revised code in a bit too! ;) — R3uK, Mar 07 '17 at 08:56
This code works perfectly for sending the current days birthdays via email column a is Names column b is emails column c is Birthdays — Clint Reid, Mar 07 '17 at 09:00
`sending_bday_greetings_method2` might work, but it cannot be called with `Variant` parameters because it accepts `string`s `ByRef`. It's the third and final time I'm willing to say it. — GSerg, Mar 07 '17 at 09:01
http://stackoverflow.com/questions/42643701/running-a-birthday-macros#comment72414641_42643701 — GSerg, Mar 07 '17 at 09:03
@ClintReid : Btw, please take a minute to see the [tour] (click it) to see how SO works! ;) — R3uK, Mar 07 '17 at 09:09
Thanks i will. Half confused about it as well but i need to brush up on my knowledge. — Clint Reid, Mar 07 '17 at 09:20

R3uK · Accepted Answer · 2017-03-07T10:36:27.440

0

Your 1st issue is with Dim vbSunday As String, vbSaturday As String.

vbSaturday and vbSaturday are constants in VBA that are numbers, and you're trying to use them as String.

Furthermore, they are most probably protected so you won't be able to use their names as variable's name.

Your second issue is with Now(vbMonday) and others, you'll need to use a function like this to get the last day from the current date :

Public Function GetLastDay(ByVal DayAsVbConstant As Integer) As Date
    GetLastDay = Now - (Weekday(Now, DayAsVbConstant) - 1)
End Function

Here is a revision of your code :

Sub send_bday_greet2()
Dim i As Long
Dim wS As Worksheet
Dim SendMessage As Boolean
Dim BirthDay As Date
'Set wS = ThisWorkbook.Sheets("Sheet1")
Set wS = ThisWorkbook.Sheets("Feuil1")

With wS
    For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row
        SendMessage = False
        BirthDay = CDate(.Range("C" & i).Value)
        Select Case True
            Case Day(Now()) = Day(BirthDay) And Month(Now()) = Month(BirthDay)
                'Birthday this day
                SendMessage = True
            Case Weekday(Now) = vbMonday And ( _
                    Day(GetLastDay(vbSaturday)) = Day(BirthDay) And _
                    Month(GetLastDay(vbSaturday)) = Month(BirthDay))
                'Birthday on Saturday
                SendMessage = True
            Case Weekday(Now) = vbMonday And ( _
                        Day(GetLastDay(vbSunday)) = Day(BirthDay) And _
                        Month(GetLastDay(vbSunday)) = Month(BirthDay))
                'Birthday on Sunday
                SendMessage = True
            Case Else

        End Select
        If SendMessage Then Call sending_bday_greetings_method2(.Range("a" & i).Value, .Range("b" & i).Value)
    Next i
End With 'wS
End Sub

And the part to send the mail :

Sub sending_bday_greetings_method2(ByVal nm As String, ByVal emid As String)
    Dim olApp As Outlook.Application
    Dim olMail As MailItem
    Set olApp = New Outlook.Application
    Set olMail = olApp.CreateItem(olMailItem)

    s = "<p> <p align='left'><font size='3' face='arial' color='blue'><i> Dear " & nm & ", </p>" & vbNewLine
    s = s & "<p> <p align='CENTER'><font size='3' face='arial' color='red'><i> We Wish you a very Happy Birthday! </p>" & vbNewLine
    s = s & "<left><p align='CENTER'><img src=""http://simplyizzy.files.wordpress.com/2012/05/happy_birthday1.png"">" & vbNewLine
    s = s & vbNewLine & "<left><p><p align='Left'><font size='3' face='arial' color='blue'><i>Regards<br>" & "Reutech Radar Systems</p>"

    With olMail
        .To = emid
        .Subject = "Happy B'day!"
        .HTMLBody = s
        .Display
        '.Send
    End With

    Set olApp = Nothing
    Set olMail = Nothing
End Sub

edited Mar 07 '17 at 10:36

answered Mar 07 '17 at 09:04

R3uK

14,417
7
43
77

What is `Now(vbSunday)` supposed to return? Also it's technically valid (although stupid) to redeclare `vbSunday`. – GSerg Mar 07 '17 at 09:09
@GSerg : Yup, my bad a good old fashioned error! ;) I'm fixing it and including it in my answer! ;) – R3uK Mar 07 '17 at 09:13
im geting the same error still. i removed my sub send bday greet2() and replaced it with yours. – Clint Reid Mar 07 '17 at 09:15
@ClintReid : Take a look at the edit ;) And please take the [tour]! – R3uK Mar 07 '17 at 09:22
@GSerg : Correction done! Thx for opening my eyes! ;) – R3uK Mar 07 '17 at 09:22
Now im getting compile error function or sub not defined `Sub send_bday_greet2()` – Clint Reid Mar 07 '17 at 09:37
Sorry i did not see the getlastday function. Thanks GUYS. IT HELPED ME ALOT – Clint Reid Mar 07 '17 at 09:38
@GSerg : I've edited to remove the `Is =` which were indeed confusing, and added the BirthDay date as variable, definitely clearer like this. I'm just confused about the last part of your comment : *I can't think of a case where it would make sense*, don't the conditions look clear enough? – R3uK Mar 07 '17 at 10:24
@R3uK The conditions do look clear enough. I was saying I can't think of a case where `Case Is = ...` would make sense over `Case ...`. – GSerg Mar 07 '17 at 10:26
@GSerg : Ok, yes indeed are the `Is =` is not necessary... So, I just have a bad reflex to type it! Will try to get rid of it! ;) Thx for all your wisdom! ;) – R3uK Mar 07 '17 at 10:29
Only problem i have left is that the email does not get sent – Clint Reid Mar 07 '17 at 10:34
Im trying to get it sent thru outlook mail – Clint Reid Mar 07 '17 at 10:35
@ClintReid : yeah, Outlook can be a bit overprotective and show a warning message or something when trying to send mails from code. Try the edit with `.Display`, to see if the message are generated properly and then retry with `.Send` to see if you have something to activate, allow or idk what! ;) – R3uK Mar 07 '17 at 10:38

Get Saturday and Sunday date to send Birthday's greeting

1 Answers1

Linked