Greetings mailer from excel sheet

[ushaanu]

HI All,

I have one excel sheet with data of multiple user mailing ids , i need to send mailer to them with one message with common image in mail body.

pls help me on this.

mailer Results need like below.

Subject Of the mail: Happy New Year

Dear Mr .
Greetings from Service!!!

We are grateful for your trust and support in our products and services, Team wishes you a very happy new year.



Signature takes from outlook.

TIA

Anu

 

[Monty]

Hello UshaAnu!

Happy New Year in Advance!

You can easily accomplish this task using VBA in Excel

Follow Me:

1. Open Excel and press ALT + F11 to open the VBA editor.
2. Insert a new module by right-clicking on any item in the Project Explorer, choose Insert, and then Module.
3. Copy and paste the following VBA code into the module:

Sub SendNewYearMailer_2024()
    Dim OutlookApp As Object
    Dim OutlookMail As Object
    Dim ws As Worksheet
    Dim cell As Range
    Dim recipients As String
    Dim imagePath As String
    
    ' Set the path to your common image
    imagePath = "C:\Path\To\Your\Image.jpg"
    
    ' Create a new Outlook application
    Set OutlookApp = CreateObject("Outlook.Application")
    
    ' Set the worksheet with email addresses
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
    
    ' Loop through each cell in column A, starting from row 2
    For Each cell In ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
        ' Build a list of recipients
        recipients = recipients & cell.Value & ";"
    Next cell
    
    ' Remove the trailing semicolon
    recipients = Left(recipients, Len(recipients) - 1)
    
    ' Create a new mail item
    Set OutlookMail = OutlookApp.CreateItem(0)
    
    ' Add recipients, subject, and body to the mail
    With OutlookMail
        .To = recipients
        .Subject = "Happy New Year"
        .Body = "Dear User," & vbCrLf & vbCrLf & _
                "Wishing you a Happy New Year! May it be filled with joy, peace, and prosperity." & vbCrLf & vbCrLf & _
                "Best regards," & vbCrLf & vbCrLf & _
                "Your Name"
        .Attachments.Add imagePath, 1 ' 1 represents olByValue (attach by value)
        .Display ' Use .Send instead of .Display to send the mail automatically
    End With
    
    ' Release Outlook objects
    Set OutlookMail = Nothing
    Set OutlookApp = Nothing
End Sub

1. Modify the imagePath variable with the actual path to your common image.
2. Change the sheet name "Sheet1" in the code to the actual name of your worksheet if it's different.
3. Customize the email body as needed.
4. Optionally, change .Display to .Send if you want to send the emails automatically without previewing them.
5. Remember to save your workbook and run the macro by pressing F5 or using the "Run" option in the VBA editor.

This code uses Outlook to send emails, so make sure Outlook is properly configured on your machine. Additionally, keep in mind that sending emails programmatically may require permission settings in Outlook.


Best of Luck!