Consulting

Results 1 to 6 of 6

Thread: Send mail by selecting email id

  1. #1
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    155
    Location

    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
    Attached Files Attached Files

  2. #2
    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.
    Attached Files Attached Files
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    155
    Location
    Dear Graham Mayor,

    Your code is working great.
    Thanks for your support.

  4. #4
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    155
    Location
    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

  5. #5
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  6. #6
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    155
    Location
    Dear Graham Mayor,

    The code is working now.
    Thanks for your reply.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •