Email Help

[smartguy]

Hello all,

I have excel file in the below format.

Excel Workbook
	A	B	C
1	Name	D.O.B	Email
2	Kavi	15-Mar	kavi@ty.com
3	Subu	29-Nov	subu@ty.com
Sheet1

Based on Date of Birth mail needs to send automatically.

It is possible?

 

[Mike Blackman]

Are you looking to send a mail out to people on there birthdays?

 

[smartguy]

yes....

 

[Mike Blackman]

Maybe something like this then....

Sub Mailer()

    Dim OutApp As Object, OutMail As Object
    Dim rngCell As Range
               
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    
    For Each rngCell In Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
        If DateSerial(Year(Date), Month(rngCell.Value), Day(rngCell.Value)) = Date Then
            With OutMail
                .To = rngCell.Offset(0, 1).Value
                .CC = ""
                .BCC = ""
                .Subject = "Happy Birthday"
                .Body = "Blah Blah Blah"
                .Display
            End With
                            
            Set OutMail = Nothing
            Set OutApp = Nothing
                               
            With Application
                waitTime = TimeSerial(Hour(Now()), Minute(Now()), Second(Now()) + 1)
                .Wait waitTime
                .SendKeys "%s~~~"
            End With
        End If
    Next rngCell
                
End Sub

Beware the .SendKeys line will send the emails, for testing it might be worth commenting this line out until your happy its working as expected.

 

[smartguy]

How it will send automatically?

i need to create button?

 

[Mike Blackman]

I mean, within the code, the sendkeys physically dispatches the email otherwise you end up with x no of open email items on your desktop.

Yes a button or event to trigger.

 

[smartguy]

I have one more Question.

The Excel sheet is always open.( all the days) it is possible the mail send automatically with out any manual work?

 

[Mike Blackman]

I believe you can do it with an OnTime Event but i've not done it before, have a search of the board and that should help.

 

[smartguy]

Ok Thanks....

 

[smartguy]

Hi I got any error.

.To = rngCell.Offset(0, 1).Value