none
Outlook 2010 - VBA replace variable with recipient email address RRS feed

  • Question

  • I can't seem to piece together the VBA code nor have I been able to find software or an Outlook add-on that can search and replace certain text in a signature with the recipient's email address.

    I have several signatures in Outlook 2010. I have a HTML version and a plain-text version of each. Each signature contains the following unsubscribe link:

    <a href="https://www.mysite.com/opt-out/?email={{RECIPIENT}}">Unsubscribe</a>

    Is it possible to have VBA replace the variable {{RECIPIENT}} with the email address of each recipient in the TO, CC and/or BCC fields?

    I'm not using AD.

    Thanks,

    Jeff




    • Edited by jeffshead Thursday, November 23, 2017 12:35 AM
    • Moved by Steve Fan Friday, November 24, 2017 7:48 AM relocate
    Thursday, November 23, 2017 12:33 AM

All replies

  • Hi Jeff,

    Welcome to the Microsoft Office for IT Professionals Outlook forum. This forum is for general non-programming questions related to Microsoft Outlook. Since your question is more related to VBA, I'll move it to the dedicated Outlook for Developers forum:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=outlookdev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Steve Fan


    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Friday, November 24, 2017 7:48 AM
  • Hello Jeff,

    To replace the variable with the email address of recipient, please work with html format the mail body.

    E.g. 

    Dim m As mailItem
    Set m = Application.ActiveInspector.CurrentItem
    m.HTMLBody = Replace(m.HTMLBody, "{{RECIPIENT}}",  m.To & " " & m.CC & " " & m.BCC)

    Please visit Chapter 17: Working with Item Bodies for more information.

    Regards,

    Celeste 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Saturday, November 25, 2017 6:47 AM
    Moderator
  • Thank you! I was able to get the following to partially work:

    Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
    Dim m As MailItem
    Set m = Application.ActiveInspector.CurrentItem
    m.HTMLBody = Replace(m.HTMLBody, "%7b%7bRECIPIENT%7d%7d", m.To & " " & m.CC & " " & m.BCC)
    End Sub

    I did visit the URL you posted but I'm still not able to piece it all together.

    Using the code above, replaces the variable with every name (not email address) in the TO, CC and BCC fields. This is not what I need. The variable needs to be replaced with the email address of each recipient. In other words, if I have both "user1@email.com" and "user2@whatever.com" in the TO field, then the email each recipient receives should contain an unsubscribe link with their email address and no one elses.

    Also, the code above does not work if the email being sent is plain text. I need the code to handle plain text and HTML signatures/emails.


    • Edited by jeffshead Saturday, November 25, 2017 4:45 PM
    Saturday, November 25, 2017 1:54 PM
  • Hello,

    Do you want to achieve that if you send one mail to A,B,C at the same time, they receives different content like A got A's mail address in the body, B got B's mail address? I think it is impossible to achieve this in your side. You may create add-ins for users to check received item and change the variable to their address.

    For one recipient, to get the email address, please visit Obtain the E-mail Address of a Recipient

    Sub GetSMTPAddressForRecipients(mail As Outlook.mailItem)
        Dim recips As Outlook.Recipients
        Dim recip As Outlook.Recipient
        Dim pa As Outlook.PropertyAccessor
        Const PR_SMTP_ADDRESS As String = _
            "http://schemas.microsoft.com/mapi/proptag/0x39FE001E"
        Set recips = mail.Recipients
        For Each recip In recips
            Set pa = recip.PropertyAccessor
            Debug.Print recip.Name & " SMTP=" & pa.GetProperty(PR_SMTP_ADDRESS)
        Next
    End Sub

    >>the code above does not work if the email being sent is plain text. I need the code to handle plain text and HTML signatures/emails.

    If you create the mail in plain text format, all the hyperlinks would be removed, so users can only receive the word "Unsubscribe". You have to use HTML or RTF. To edit signature for HTML and RTF at the same time, you could use Word Editor.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Sunday, November 26, 2017 8:36 AM
    Moderator
  • >>Do you want to achieve that if you send one mail to A,B,C at the same time, they receives different content like A got A's mail address in the body, B got B's mail address?

    Yes, that's correct. I thought it would be possible to automate the creation of a customized unsubscribe link for each email being sent out.

    For plain text emails, I wanted to replace "Unsubscribe" with the same unsubscribe URL with their email address. Most email readers turn plain text URL's into links.

    Cheers,

    Jeff


    • Edited by jeffshead Sunday, November 26, 2017 12:01 PM
    Sunday, November 26, 2017 11:58 AM
  • Hello,

    >> I thought it would be possible to automate the creation of a customized unsubscribe link for each email being sent out.

    However, you actually send one mailitem even there are several recipients, so it is impossible to customize one mail to several different versions. If it is possible for several recipients, what value would you see in your sent box?  

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 27, 2017 1:41 AM
    Moderator