Compile error user defined type not define

**HaHoBe** · 11-13-2013, 01:16 AM

Hi, Mukesh,

even if the birthday is reckognized there won´t be a valid email-address as this is located in Column M whereas you work on the range starting on Column O.

Code for ThisWorkbook:

Private Sub Workbook_Open()
With Sheets("personal").Range("b4")
  If .Value < Date Then
'    Call OpenOutlook
    Call MemberBirthday_131113
    .Value = Date
  End If
End With
End Sub

Code for a standard module (here Modul1):

Sub MemberBirthday_131113()
Dim x, i&, OutApp As Object
Dim Msg1 As String, Msg2 As String, Msg3 As String, Msg4 As String, Msg5 As String
Dim Msg6 As String, Msg7 As String, Msg8 As String, Msg9 As String, Msg10 As String
Dim Msg11 As String, Msg12 As String, Msg13 As String

With Sheets("Personal")
  x = .Range("o8:Z" & .Cells(Rows.Count, "p").End(xlUp).Row + 1).Value
End With
          
Msg1 = "WISH YOU HAPPY BIRTHDAY!"
Msg3 = """Hope your Special day"
Msg4 = "Brings you all that"
Msg5 = "Your heart desires"
Msg6 = "Here's wishing you a day"
Msg7 = "Full of pleasant surprises!"
Msg8 = "Happy Birthday"""
Msg9 = "I have a great pleasure to convey my best wishes on the occasion of your Birth Day!"
Msg10 = """May God offer you a very Healthy and Wealthy life ahead!!"""
Msg11 = "With regards,"
Msg12 = "Mukesh Baviskar"
Msg13 = "Mobile:   1234554321  "

For i = 1 To UBound(x)
  If IsDate(x(i, 2)) Then
    If Cells(7 + i, 32).Value = "Send mail" Then
      If Month(x(i, 2)) = Month(Date) And Day(x(i, 2)) = Day(Date) Then
        If Cells(7 + i, "M") > 2 Then
          MsgBox x(i, 1) & "'s Birthday Today!", 64
          '   Create Outlook object
          Set OutApp = CreateObject("Outlook.Application")
          Msg2 = "Dear " & x(i, 1) & ","
          'Create Mail Item and view before sending
          With OutApp.CreateItem(0)
            .To = Cells(7 + i, "M") 'EmailAddr
            .Subject = "Happy Birthday"
            '.HTMLBody = "<center><img src='C:\Cake.jpg'></center><br><br>" 'Insert the full windows path of your image here
            '.HTMLBody = .HTMLBody & "<font color='red'>"
            .HTMLBody = "<html>" & "<center><b><i><font face=""Comic Sans MS"" size=""3"" color=""red"">" & Msg1 & "</font></i></b></center><br/><br/>" & _
                    "<center><img src='C:\Cake.jpg'></center><br><br>" & _
                    "<center><b><font face=""Comic Sans MS"" size=""2"" color=""green"">" & Format(Date, "dd mmmm yyyy") & "</font></b></center><br/><br/>" & _
                    "<font face=""Comic Sans MS"" size=""2"" color=""FF1CAE"">" & Msg2 & _
                    "<center><b><font face=""Comic Sans MS"" size=""2"" color=""blue""><br/><br/>" & _
                    Msg3 & "<br>" & Msg4 & "<br>" & Msg5 & "<br>" & Msg6 & "<br>" & Msg7 & "<br>" & Msg8 & _
                    "</font></b></center><br><br><font color='FF1CAE'>" & Msg9 & "<br><br>" & Msg10 & "<br><br>" & _
                    "<font face=""Comic Sans MS"" size=""2"" color=""red"">" & Msg11 & "<br><br>" & Msg12 & "<br><br>" & Msg13 & "</font>"
            '.Attachments.Add ("c://Happy Birthday.xlsm")  ' for example
            .Display
            .Send
          End With
          Set OutApp = Nothing
          '-------------------  End Creating Email using outlook ---------------
        Else
          MsgBox x(i, 1) & "'s Birthday Today! & has empty E-mail address", 48
        End If    'If Len(x(i, 8)) > 2 Then
      End If
    End If
  End If
Next i
MsgBox "The task completed successfully!", 64
End Sub

You should consider to use the Autofilter for Column AF and use "Send mail" as criteria. Any further action could be to check the number of the last visible cell to find out if any birthdays are on that day and then use a For Each and loop through the visible cells by using SpecialCells(xlCellTypeVisible) in one Column.

Ciao,
Holger