Automatic Birthday Mailer

This blog will help you send automatic mailer’s for birthdays, work anniversaries, marriage anniversaries, etc. Assuming almost everyone has Microsoft office in your systems, it is built on Microsoft Excel VBA platform.

These are the following options available on the tool:
1. It can send random customized messages for everyone on the list.
2. Attach a birthday Picture for everyone.
3. Set any Gmail/Yahoo/Office email id to send the mails.
4. Keep a bcc mail id to track if things are working fine.
5. Save the status of the last execution instance.

There is a batch file which opens the MS Excel file. Use the windows task scheduler for triggering the batch file on a particular time daily. Best is to keep the files on a server that is running all the time. And schedule the task on the server. Fill the details in the excel files as per your requirement.
Select pictures that you want to send as attachment.

Keep all the pictures in the Attachment folder, give all the file names a sequential ordering. It will be easier to identify in the excel file. Select images for similar type, i.e. same file extension, it’s easier to update later.

Select at least 20% unique messages. Add them to the MESSAGE sheet. Make sure to update the serial number in the MESSAGE sheet. Similar thing can be dome for customizing the subject lines.

Update the formula for the attachment and body according to your requirement.
The syntax would be:
=RANDBETWEEN(1,<number of unique attachments>)&".<FileExtension>"
=INDEX(Message!$B$2:$B$<row number of last item>,RANDBETWEEN(1,<number of unique messages>))

CODE MODIFICATION:
Open the Visual Basic Editor > Go to ThisWorkbook code section.
1. To send the mail from your laptop/Server configured outlook
    Call Module1.automated_email
2. To send the mail from gmail ID
    Call Module2.automated_email_gmail
3. To commit your execution instance
    ActiveWorkbook.Save
4. To close the workbook after execution
    ActiveWorkbook.Close

In the module2 update this two lines with your specific detail
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "SenderEmail@gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "SenderEmailPassword"

Here the SenderEmail ID has to be a Gmail ID because the configuration in module 2 is set as per Gmail’s SMTP configuration,

If you want to send mail from Yahoo Mail the go to this link to get the SMTP configuration
http://warlocksolutions.blogspot.in/2017/08/automated-emails-from-excel-via-YahooMail.html

If you have your own Mail Server then best way is to use the Outlook method for sending. OR you can ask the server configuration details from your mail admin and update the same in the code.

IMPORTANT THING TO REMEMBER, FOLLOW STEP WISE:
1. The Attachments folder should be in the same directory where the BirthdayMailer file is placed.
2. After you update the file with new birthday details SAVEAS the file with current date.
3. In the code inside ThisWorkbook, Uncomment 1 OR 2 (Outlook or Gmail) as per your need. Also uncomment 3 AND 4 (Save and Close).
4. SAVEAS the file without current Date (BirthdayMailer.xlsm), after you close this file you cannot open this file anymore because the file will open, execute the code, save and then auto close. Replace the file in the laptop/server with this file.
5. In order to update new details next time, use the file with the date in the filename and repeat from step 1

HOW TO SET UP THE TRIGGER:
1. Right click on the batch file (trigger.bat) > Edit, Update the code and save it:
START EXCEL.EXE "<YOUR DESIRED LOCATION>\BirthdayMailer.xlsm"
2. Go to windows Task Scheduler and create a Basic Task:
3. Give a name to the Task. Ex: Birthday
4. Trigger Daily, Set the time as per your requirement.
5. Action > Start a program
6. Program/script: Browse the trigger.bat file
7. Select “Open the properties dialog for this task…” before you click finish.
8. In the properties window, update the run settings as per your requirement and save.
I would suggest keep all the files in one folder in the server.
1. BirthdayMailer.xlsm
2. trigger.bat
3. Attachments Folder

For any other queries please use the comments section below.

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.

SUBHAJIT.

LINK FOR THE EXAMPLE FILE

Sending Automated emails from Excel via YahooMail

Hello Everyone,

I have written other blogs on sending mail from Excel using VBA via Outlook and Gmail.

This blog contains the code for sending mails from Excel using VBA via YahooMail.
This is tested from a personal free yahoo account.
Here is the code for the same:


Sub automated_email_yahooMail()
    
    Dim mail As CDO.Message
    Set mail = New CDO.Message

    With mail.Configuration.Fields
   
       '1. Setting SSL Authentication
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
       '2. Setting SMTP Authentication
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
       '3. Setting SMTP Server and Port
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.mail.yahoo.com"
       .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
       
       
       .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        
       'USERID AND PASSWORD
       .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "emailID@yahoo.com"
       .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
        
       'Don't forget to Update the configuration fields
       .Update
    End With
    
    With mail
       .To = "emailID1@domain.com"
       .From = "emailID@yahoo.com"
       .Sender = "Warlock Solutions"
       .CC = "emailID2@domain.com"
       .BCC = "emailID3@domain.com"
       .Subject = "Test mail from VBA via YahooMail"
       .textbody = "Please respond if you've received."
       .AddAttachment "Path\attachmentName.attachmentExtension"
       .Send
    End With
    
    Set mail = Nothing
End Sub

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.

SUBHAJIT.

Sending Automated emails - WITH GENERIC AND CUSTOMIZED CONTENT Via Outlook

Hello Everyone,


Long time I have written a blog on Excel VBA.

This blog contains details regarding sending automated emails from an excel template, which is a common thing and you'll find many other blogs for the same.


But What's different in this is:

1. You can use customized names for few user and the generic name for the rest.
2. Send a copy of the mail to an individual person or just CC to the generic address.
3. You can use customized subject for few user and the generic subject for the rest.
4. You can have customized message body for few user and the generic content for the rest.
5. You can use customized signatures for few user and the generic signature for the rest.
6. You can also have attachments for few users.

Here is the code for the same :

Sub automated_email()
    
    Dim counter As Long
    
    Dim outlookApp As Outlook.Application
    Dim outlookMail As MailItem
    Set outlookApp = New Outlook.Application
    
    Dim bodyString As String
    
    For counter = 5 To formatEmail.Cells(formatEmail.Rows.Count, "A").End(xlUp).Row

        Set outlookMail = outlookApp.CreateItem(outlookMailItem)
        outlookMail.To = formatEmail.Range("B" & counter).Value
        
        If formatEmail.Range("C" & counter).Value <> "" Then
            outlookMail.CC = formatEmail.Range("C" & counter).Value
        Else
            outlookMail.CC = formatEmail.Range("C2").Value
        End If
        
        outlookMail.BCC = formatEmail.Range("D" & counter).Value
        
        If formatEmail.Range("E" & counter).Value <> "" Then
            outlookMail.Subject = formatEmail.Range("E" & counter).Value
        Else
            outlookMail.Subject = formatEmail.Range("E2").Value
        End If
        
        bodyString = "Hi "
        
        If formatEmail.Range("A" & counter).Value <> "" Then
            bodyString = bodyString & formatEmail.Range("A" & counter).Value & "," & vbNewLine & vbNewLine
        Else
            bodyString = bodyString & formatEmail.Range("A2").Value & "," & vbNewLine & vbNewLine
        End If
        If formatEmail.Range("G" & counter).Value <> "" Then
            bodyString = bodyString & formatEmail.Range("G" & counter).Value & vbNewLine & vbNewLine
        Else
            bodyString = bodyString & formatEmail.Range("G2").Value & vbNewLine & vbNewLine
        End If
        If formatEmail.Range("H" & counter).Value <> "" Then
            bodyString = bodyString & formatEmail.Range("H" & counter).Value & vbNewLine & vbNewLine
        Else
            bodyString = bodyString & formatEmail.Range("H2").Value & vbNewLine & vbNewLine
        End If
        
        outlookMail.Body = bodyString
        
        If formatEmail.Range("F" & counter).Value <> "" Then
            If Dir(formatEmail.Range("F" & counter).Value) <> "" Then
                outlookMail.Attachments.Add formatEmail.Range("F" & counter).Value
            End If
        End If
        
'        Use outlookMail.Display to review before sending
        outlookMail.Display
        
'        Use outlookMail.Send to directly send without reviewing
'        outlookMail.Send
        
    
        Set outlookMail = Nothing
    Next
       
    Set outlookApp = Nothing
    
End Sub

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.
SUBHAJIT.
.
To Download the example file:
Please click this Google Drive Link