Consulting

Results 1 to 2 of 2

Thread: Please Help ! "Birthday Emails to be sent on Sat/Sun"

  1. #1
    VBAX Newbie
    Joined
    Oct 2022
    Posts
    1
    Location

    Question Please Help ! "Birthday Emails to be sent on Sat/Sun"

    Hi, I don't know much about coding....just the beginner.
    Want to send birthday wishes to all team members. I have got the vba coding which helps me to send the wishes with image at the time I open the excel workbook. However I am still stuck... if the birthday falls on Saturday or Sunday, then how I can figure this out. Also If i open the workbook more than once in a day ...it resend as many times I open it. Please help me with these.

    Sub Bdwishes()
    Dim i as long
    Dim ws as worksheet
    Dim sendmessage as boolean
    Set ws= this.workbook.sheets("sheet1")
    With ws
    For i=2 to .Range("A" & Rows.count).End(xlUp).Row
    Send message=True
    Birthday=Cdate(.Range("C" & i).value
    Select Case True
    Case Day(now())= Day(Birthday) And Month(Now()) = Month(Birthday)
    'birthday this day
    Sendmessage=True
    End Select
    If SendMessage Then Call SendBDay(Range("a" & i).value, Range("b" & i)
    next i
    End with
    End Sub
    Last edited by Aussiebear; 10-18-2022 at 02:38 AM. Reason: Added code tags to supplied code

  2. #2
    The following will send a message (insert as appropriate) if the worksheet is opened on a weekday and a message has not already been sent. The code adds the year to column D to record the fact that this year the message has been sent. Note Sunday is weekday1 and Saturday is weekday 7, so you can use this to determine what to do with birthdays that fall on the weekend.


    Sub Bdwishes()
    Dim i As Long
    Dim ws As Worksheet
    Dim bSendMessage As Boolean
    Dim dBirthday As Date
        Set ws = ThisWorkbook.Sheets("Sheet1")
        With ws
            For i = 2 To .Range("A" & Rows.Count).End(xlUp).Row
                bSendMessage = False
                dBirthday = CDate(.Range("C" & i).Value)
                Select Case True
                    Case Day(Now()) = Day(dBirthday) And Month(Now()) = Month(dBirthday)
                        'birthday this day
                        bSendMessage = True
                End Select
                If bSendMessage Then
                    If Weekday(dBirthday) > 1 And Weekday(dBirthday) < 7 Then
                        'the birthday is a weekday
                        If .Range("D" & i).Value < Year(Now) Then
                            'send birthday message
                            MsgBox "send message"
                            'update year message sent
                            .Range("D" & i).Value = Year(Now)
                        End If
                    End If
                End If
            Next i
        End With
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •