+ Reply to Thread
Results 1 to 5 of 5

VBA Outlook Email: Replace a text in the body by a cell value

  1. #1
    Registered User
    Join Date
    10-31-2019
    Location
    Montreal
    MS-Off Ver
    2016
    Posts
    17

    VBA Outlook Email: Replace a text in the body by a cell value

    Hi guys,

    I am working on an Excel file to send emails to clients using VBA. I am a complete beginner
    I would like to know how to replace a value in my Outlook template by a value from excel.

    For example: I want to replace the word #Client# by the cell value where the client name is.

    Somehow, I managed to do this after many searches but as you can see there are no replace lines.

    Thank you for your help!!!

    Sub Email_From_Excel_Attachments()


    Dim emailApplication As Object
    Dim emailItem As Object
    Dim path As String

    Set emailApplication = CreateObject("Outlook.Application")
    Set emailItem = emailApplication.CreateItemFromTemplate("C:\Users\ClaudiaR\Desktop\1.oft")



    'Change to work email

    emailItem.SendUsingAccount = "work@hotmail.com"

    emailItem.to = Range("R9").Value

    emailItem.BCC = Range("R22").Value

    'Subject
    emailItem.Subject = "Business Appointment"

    ' Attach any file from your computer.
    'emailItem.Attachments.Add Range("R34").Value





    ' Send or Display the Email
    emailItem.Display

    Set emailItem = Nothing
    Set emailApplication = Nothing

    End Sub

  2. #2
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: VBA Outlook Email: Replace a text in the body by a cell value

    I assume #Client# is in the body of the template.

    and let the name be in cell "A1", then add this to your code

    Please Login or Register  to view this content.
    cheers

  3. #3
    Registered User
    Join Date
    10-31-2019
    Location
    Montreal
    MS-Off Ver
    2016
    Posts
    17

    Re: VBA Outlook Email: Replace a text in the body by a cell value

    Thank you so much gue2013! It works

    Is it possible to have the template taken from a cell value? I can copy the file path in a cell. I wanna add an option to change the template based on client's language.

    Set emailItem = emailApplication.CreateItemFromTemplate("C:\Users\ClaudiaR\Desktop\1.oft")
    Last edited by ClaudiaRovastino; 04-16-2020 at 08:21 AM.

  4. #4
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: VBA Outlook Email: Replace a text in the body by a cell value

    look into the attachment

    I defined a list that include templates (as many as you like)
    I also define a named range ("OUTLOOK_TEMPLATE") with data validation (dropdown with selection of your table of templates)
    then you can use following statement
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-31-2019
    Location
    Montreal
    MS-Off Ver
    2016
    Posts
    17

    Re: VBA Outlook Email: Replace a text in the body by a cell value

    Thank you so much. I really appreciate your help!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Generating Outlook Email and inseting chart into email with Text body.
    By Hyperion1571 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-17-2019, 12:41 PM
  2. Extract multiple lines of text from Outlook email body into Excel
    By Ranger75 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-07-2018, 03:47 PM
  3. [SOLVED] I need the body of my Outlook email to look like the source worksheet (wrapped text)
    By aliciaward1001 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-03-2018, 12:18 PM
  4. Unable to send user-editable text to Outlook email body based on active cell.
    By m0meehan in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-20-2014, 01:51 PM
  5. Add to Excel from Outlook when a email body contain a specific text
    By mathewfer in forum Outlook Programming / VBA / Macros
    Replies: 0
    Last Post: 05-08-2013, 08:28 AM
  6. Using Excel vba to send hyperLinks in the body text of an Outlook email using a checkbox
    By PatrickASnyder in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-29-2013, 04:13 PM
  7. Outlook : Auto Save Email body to Word / Text
    By sgcareerman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-04-2012, 10:23 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1