Auto send birthday email using vba

[shopaholic]

Hi,
I followed the solution provided by Jim May in this thread http://www.mrexcel.com/forum/excel-...atic-birthday-wishes-using-excel-outlook.html and it worked perfectly.

[COLOR=#333333]Sub DoBirthdayRoutine()[/COLOR]
Dim olApp As Outlook.ApplicationDim MItem As Outlook.MailItemDim cell As RangeDim Subj As StringDim EmailAddr As StringDim Msg As StringSet olApp = New Outlook.ApplicationApplication.ScreenUpdating = FalseSheets("Sheet1").ActivateLR = Range("B" & Rows.Count).End(xlUp).RowFor Each cell In Range("B2:B" & LR)    If Month(cell) = Month(Date) And Day(cell) = Day(Date) Then    Pos = WorksheetFunction.Find(" ", cell.Offset(, -1))    FName = Left(cell.Offset(, -1), Pos - 1)    Subj = "Happy B'day"    EmailAddr = cell.Offset(, 1).Value    Msg = "Dear " & FName & "," & vbNewLine    Msg = Msg & vbNewLine & " Happy Birthday to you and many more happy returns.  Have a wonderful day." & vbCrLf & vbCrLf    Set MItem = olApp.CreateItem(olMailItem)With MItem    .To = EmailAddr    .Subject = Subj    .Body = Msg    .SendEnd WithEnd IfNextApplication.ScreenUpdating = True [COLOR=#333333]End Sub
[/COLOR]

However, I have a few more requirements -
I wanted to embed an image below the birthday wishes text.
Solution- I added CC BCC code and then used htmlBody to change font color, size, etc. it is working good.
Now -
1) I want to automate this email sending - I read about CDO but don't know how to go about it.
2) How can I make this vba independent of system state? (I know already this is next to impossible, can it run even if system is not ON?)
3) I want to make it send birthday wishes to people who have their birthday on weekends and holidays also.

I'm focusing on understanding CDO meanwhile expect some help.
Thanks.

 

[shopaholic]

no reply?

 

[scott_n_phnx]

To respond to your questions

1)You can automate the email to send out on a specific date using a Worksheet_Change event, but
2)It can only run while the workbook is open, but
3)There are methods to create an email ahead of time and be scheduled to be sent on a specific date, if you are using an enterprise version of Outlook. (POP3 and IMAP require Outlook to remain open to delay or schedule emails)

All of this would take a bit of preparation to sort things out.


EDIT--Scratch that. I was unaware of being able to send emails through Excel without Outlook, using CDO. With that said, my answer to 1 still stands, and from what I read, 2 as well, but 3 would depend on what email client that you are using. Some may allow the delay in sending emails, while others do not.

 

[shopaholic]

@scott_n_phnx -

Thanks for the reply.
1) Emails are being sent by matching DOB value with today's date. This happens when I run the macro manually! I can put the macro in open worksheet event and write a batch file to open the excel file once everyday at a specific time by creating a scheduled job! But problem is - I will have to leave my system ON all the time which is not feasible!

I'm using enterprise version of outlook. :P

 

[scott_n_phnx]

For the delay or scheduling with Outlook, take a look here. Delay or schedule sending email messages - Outlook

 

[shopaholic]

Thanks, it would be of help I will just have to find a way to let the macro set this option. :P But it all again comes down to system remaining in on state so that mails can be sent.

 

[scott_n_phnx]

If you already have a code built, you can add this into it.

<b>.DeferredDeliveryTime = Date</b>

Like this

Sub SendMail()
Dim OutApp As Object
Dim Outmail As Object
Dim strBody As String
Set OutApp = CreateObject("Outlook.Application")
OutApp.session.logon
Set Outmail = OutApp.CreateItem(0)
strBody = "Test"
On Error Resume Next
With Outmail
    .To = "test@test.com" 'change "test@test.com" into "yourownmailadres@mail.com" to make the macro really work
    .CC = ""
    .BCC = ""
    .Subject = "Test - No Reply - Automatic mail"
    .HTMLBody = strBody
    .Send
	<b>.DeferredDeliveryTime = Date</b>
End With
On Error GoTo 0
Set Outmail = Nothing
Set OutApp = Nothing
End Sub

 

[deepaklohia]

there is a ready made automation available on youtube . you can just download the sample .
https://www.youtube.com/watch?v=1jFJ35ZPR_A