Change reply address in Outlook mail

Question

I have an Excel sheet with three columns Employee Name, Email ID and DOB.

I wrote a macro which matches birth dates of the employees with today's date that will send an Outlook mail To the employee and Cc my department.

When all the employees see that mail they can click on reply or reply to all.

I wrote another Outlook macro which replaces To address field with his/her birthday person email id.

The second macro is working on my system on any Outlook email which is open.

Because I have the Outlook macro I am able to execute it but to perform same thing in all the employees systems they need this Outlook macro. How can I run it in their systems without putting this macro in their systems manually?

If you have spaces at the beginning of a line, the line is formatted as code. I have removed the spaces so your question can be read. — Tony Dallimore

Tony Dallimore Tony Dallimore · Accepted Answer · 2012-02-13T11:39:21

The following code assumes ObjMail is the message you are creating.

' Delete any existing reply recipients
Do While ObjMail.ReplyRecipients.Count > 0
  ObjMail.ReplyRecipients.Remove 1
Loop

' Add the new recipient
ObjMail.ReplyRecipients.Add "[email protected]"

' Send blind copy to other staff members
ObjMail.BCC = "Staff1.isp.com, Staff2.isp.com, Staff3.isp.com"

The message sent to staff will say it has come from whoever sends the birthday messages. But if anyone replies, the recipient will be "[email protected]".

I have sent blind copies to other staff members. This is not because the staff list is secret but because:

If you have 500 staff members with an average 20 characters per address, use of CC would add 10,000 characters to each of 500 messages.
It prevents staff using Reply All when adding their best wishes saving another 500 * 500 messages.
Use ObjMail.CC if you would prefer to fill your company's server.

I worry about message size because many years ago I worked for the English NHS which had thousands of employees scattered across the country. Someone at a small hospital tried to advertise his bicycle for sale within the hospital but managed to advertise it to every employee in the country. I worked from home with a slow dial-up line; it took half-an-hour to download this message.

New section in response to request for full code of test routine

Below I include the full routine I used to test my answer. It was adapted from a routine I wrote for another answer. It creates an HTML body which you may not want but shows you how if you do. I have replaced the real email addresses I used for my tests with dummy addresses; otherwise it is unchanged.

Sub ReplyToRecipientWithBlindCopies()

  ' Create a mail item with a simple message.
  ' Send the mail item to "[email protected]" and make them
  ' the recipient of any replies.
  ' Send blind copies to all other recipients.

  ' Author: Tony Dallimore, York, England

  Dim OlApp As Outlook.Application
  Dim ObjMail As Outlook.MailItem

  Dim MessageBody As String

  ' This creates a blue message on a grey background.  This is a
  ' demonstration of what is possible; not a recommendation!
  MessageBody = "<table width=""100%"" style=""Color:#0000FF;" & _
         " background-color:#F0F0F0;""><tr><td align= ""center"">" & _
         "Happy birthday from all your colleagues!</td></tr></table>"

  Set OlApp = Outlook.Application
  Set ObjMail = OlApp.CreateItem(olMailItem)
  With ObjMail
    .BodyFormat = olFormatHTML
    .Subject = "Happy birthday!"
    .HTMLBody = HeadAndBodyToHtmlDoc("", MessageBody)

    ' Remove any existing recipients
    Do While .Recipients.Count > 0
      .Recipients.Remove 1
    Loop
    ' Remove any existing reply recipients
    Do While .ReplyRecipients.Count > 0
      .ReplyRecipients.Remove 1
    Loop

    ' Add birthday person to Recipient and ReplyRecipient lists
    .Recipients.Add "[email protected]"
    .ReplyRecipients.Add "[email protected]"

    ' You will need to replace this with a loop
    ' to add all your staff members.
    .BCC = "[email protected], [email protected], [email protected]"

    ' Display the prepared messages ready for any final changes.
    ' The user must send it.
    .Display
  End With

End Sub
Function HeadAndBodyToHtmlDoc(Head As String, Body As String) As String

  ' Wrap Head and Body created by caller in a standard envelope.

  ' Author: Tony Dallimore, York, England

  HeadAndBodyToHtmlDoc = _
        "<!DOCTYPE html PUBLIC ""-//W3C//DTD XHTML 1.0 Frameset//EN""" & _
        " ""http://www.w3.org/TR/xhtml1/DTD/xhtml1-frameset.dtd"">" & _
        vbCr & vbLf & "<html xmlns=""http://www.w3.org/1999/xhtml""" & _
        " xml:lang=""en"" lang=""en"">" & vbCr & vbLf & "<head><meta " & _
        "http-equiv=""Content-Type"" content=""text/html; " & _
        "charset=utf-8"" />" & vbCr & vbLf & Head & vbCr & vbLf & _
        "</head><body>" & vbCr & vbLf & Body & "</body></html>"

End Function

Change reply address in Outlook mail

1 Answers