Send mail by selecting email id

[elsuji]

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

[gmayor]

While personally I would use mail merge for this - see https://www.gmayor.com/email_merge_addin.html (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.

[elsuji]

Dear Graham Mayor,

Your code is working great. Thanks for your support.

[elsuji]

Dear Graham Mayor,

I have one doubt on this code.

for attaching file, presently we specified one file name


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

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

I tried this code


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

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

[gmayor]

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


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

[elsuji]

Dear Graham Mayor,

The code is working now. Thanks for your reply.