Dear friends,
Please suggest me a correction why the message body is blank.
Sub padwa()
'Send gudi padwa wishing mail to all
Dim aOutlook As Object, aEmail As Object, nm As String, msg As String
Dim rngeCell As Range, strRecipients As String
Dim arrMail, arrName
Dim lngArrCount As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each rngeCell In ActiveSheet.Range("M9:M5000").Cells
If Len(rngeCell) > 2 Then
strRecipients = strRecipients & ";" & rngeCell.Value
rngeCell.Offset(0, 2).Interior.ColorIndex = 6
nm = nm & vbCrLf & rngeCell.Offset(0, 2).Value
End If
Next rngeCell
arrMail = Split(strRecipients, ";")
arrName = Split(nm, vbCrLf)
Set aOutlook = CreateObject("Outlook.Application")
For lngArrCount = 1 To UBound(arrMail)
Set aEmail = aOutlook.CreateItem(0)
aEmail.Importance = 2
aEmail.Subject = "Happy gudi padwa!"
aEmail.To = arrMail(lngArrCount)
msg = "Dear " & Name & "," & vbNewLine & """Hope your Special day" & vbNewLine & _
"Brings you all that" & vbNewLine & _
"Your heart desires" & vbNewLine & _
"Here's wishing you a day" & vbNewLine & _
"Full of pleasant surprises!" & vbNewLine & vbNewLine & _
"Happy Birthday""" & vbNewLine & vbNewLine & _
"I have a great pleasure to convey my best wishes" & vbNewLine & _
"on the occasion of your Birth Day!" & vbNewLine & vbNewLine & _
"""May God offer you a very Healthy and Wealthy life ahead!!""" & vbNewLine & vbNewLine & _
"Please see the attachment." & vbNewLine & vbNewLine & _
"With regards," & vbNewLine & vbNewLine & _
"Mukesh Baviskar"
aEmail.Display
aEmail.Send
Set aEmail = Nothing
Next lngArrCount
Set aOutlook = Nothing
'MsgBox "Birthday Today!" & vbCrLf & Nm
MsgBox "The gudi padwa mails sent successfully!", 64
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Thank you.
Regards,
Mukesh
Bookmarks