Send mail by selecting email id

Dear Team,

I am having customer database. In that column "I" customers Email address is available.

Once i select any Email address, the outlook mail need to trigger automatically with following subject and body along with attachment

Mail Subject: GREETINGS
Mail Body:

Dear Valuable Customer,

Greetings!!

On behalf of everyone from My company, We would like to thank you for being a Customer/A Guest/An Investor.

We value the trust you have put In our products, services and would like to thank you for that. It is always a pleasure serving you and we certainly look forward to doing that in the future.

Thanks

Attachment Location: C:\Users\SENTHIL KUMAR P\Desktop\Folder\Test.pdf

Can any one help me how to do this . I am attaching my file here for your reference

While personally I would use mail merge for this - see https://www.gmayor.com/email_merge_addin.html what you ask is possible, though I would suggest selecting the username in column B rather than the e-mail address as that would trigger the hyperlink. See attached. It would be easy enough to modify the macro to personalise the message if the data was available from the worksheet.

Dear Graham Mayor,

Your code is working great. Thanks for your support.

Dear Graham Mayor,

I have one doubt on this code.

for attaching file, presently we specified one file name

Code:

---

sPath = Environ("USERPROFILE") & "\Desktop\Folder\Test.pdf"

---

If suppose i want to attach all files which is in .pdf extension

I tried this code

Code:

---

sPath = Environ("USERPROFILE") & "\Desktop\Folder\" & "*.pdf"

---

But i am getting error. Can you please explain me how to do this

You would need to loop through the files and add them e.g.

Code:

---

    sPath = Environ("USERPROFILE") & "\Desktop\Folder\"
    Set olApp = OutlookApp()

    On Error Resume Next
    Set olMail = olApp.CreateItem(0)
    With olMail
        'the recipient will need to be looked up and applied here
        .To = sAddress
        .Subject = "Greetings"
        .BodyFormat = 2    'html
        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor    'access the message body for editing
        .Display    'required to edit message body
        Set oRng = wdDoc.Range
        oRng.Collapse 1
        oRng.Text = sMessage
        sFile = Dir$(sPath & "*.pdf")
        While sFile <> ""
            .Attachments.Add sPath & sFile
            sFile = Dir$()
        Wend
        '.Send 'restore after testing
    End With

---

Dear Graham Mayor,

The code is working now. Thanks for your reply.