0

I'm making a VBA userform that replies to the active Outlook email with a template (different templates based on listbox choices). The problem right now is that when I "reply all" it is grabbing just the first and last name of the sender and recipients.

The senders are primarily outside the company, so I need it to grab and populate the "To" field with the actual email addresses. If it were only in-company the users would be in the company directory and it wouldn't be an issue. The closest I've come to finding this is the answer to How do you extract email addresses from the 'To' field in outlook?. I feel like the information I need is available there (only explicitly deals with grabbing info for recipients but I figure the same principle will apply to the sender), but I can't make sense of how to insert it into my code for the desired result.

Here's what I am starting from:

Private Sub CommandButton1_Click()

Dim origEmail As MailItem
Dim replyEmail As MailItem

Set origEmail = ActiveExplorer.Selection(1)
Set replyEmail = CreateItemFromTemplate("C:\Download Tool\Need Stat Code X.oft")

replyEmail.To = origEmail.ReplyAll.To

replyEmail.HTMLBody = replyEmail.HTMLBody & origEmail.Reply.HTMLBody
replyEmail.SentOnBehalfOfName = "emailaddress@mycompany.com"
replyEmail.Display

Set origEmail = Nothing
Set replyEmail = Nothing

End Sub

The emails are populating and I'm getting nearly all the info I want, but I haven't found a clear explanation of how to grab & insert the email addresses.

Thanks for your time and advice!

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
JCB
  • 1
  • 5
  • The question to which this is an answer is nothing like yours: http://stackoverflow.com/a/40193624/973283. However, it includes a macro `DemoExplorer` which I use to examine the properties of selected emails in a convenient manner. You may find it helpful as an experimental tool. I would look at the Recipients property (as recommended by Hrothgar) and the CC property. CC is a string. Recipients is a collection (like Attachments) so treat like an array with lower bound 1 and upper bound Recipients.Count. – Tony Dallimore Nov 18 '16 at 21:23
  • Note: an HtmlBody probably contain a header and a body section. You are just concatenating the entire strings rather than the sections. Some email packages will accept any old rubbish and make a good fist of displaying it but some expect properly structured Html. – Tony Dallimore Nov 18 '16 at 21:28
  • Thanks for linking that macro! I'm sure I'll find it useful in the future. As for right now, I'm not seeing how it can be applied to pulling the SMTP address for use in a reply all. It's not that I don't know what email addresses to reply to, it's that I don't know how to populate them via my macro. As noted in my response to Hrothgar, I'm piecing this together as I go and if there is something I overlooked in DemoExplorer that I've been too obtuse to notice please feel free to beat me over the head with it. – JCB Nov 21 '16 at 22:46
  • That version of `DemoExplorer` does not display CC and Recipient addresses because I did not need them for that answer. I add the code to output whatever properties I need to look at today. I was not clear that I was telling you that CC is a string and Recipients is a Collection so you could add them to `DemoExplorer`. Perhaps CC and Recipients contain the information you need. If not, there are "Extended Properties" which might contain the information you need. – Tony Dallimore Nov 22 '16 at 01:01

2 Answers2

0

If you put a stop after "Set origEmail = ..." and set a watch on origEmail, you will see the properties of the email. Included is the Recipients collection. There are two (that I can see) types, SMTP and EX. For me, EX means internal. in each item in the recipients items is a property called address and another called addressentry. The addressentry part contains the address type.

deep breath

OK, so you need to be able to convert the EX addresses into internal addresses by parsing the part at the end, and you can just put the SMTP ones in as is, I think. Build a string of the addresses in the recipients address list and put it in the To and/or CC fields and you should be good. The To or CC part is the recipients (n).type property...

I think.

Gosh, I hope someone posts an easier way to do it :)

Hrothgar
  • 412
  • 2
  • 5
  • This sounds like what I'm trying to do, but I'm still unsure of how to do it. I am a novice with VBA, and use the information provided as a platform to figure out how to code what I need...but I'm coming up with nothing. How do I "set a watch on" origEmail? Also, I'm not concerned with the internal EX addresses. They can be handled as SMTP for all I care (unless there is something that would cause a problem that I'm not noticing). I just don't know how to call the SMTP addresses so that I can populate my reply's "To" field with them. I greatly appreciate your assistance in understanding. – JCB Nov 21 '16 at 22:29
  • In the VBA editor, you can step through the code one line at a time using the F8 key. At any point in the program you can examine the contents of a variable by setting a watch. Left click on the variable in the code, and click "add Watch." This will open up the watch window (or you can do so in the view menu) and as you step through the code, you can see the variable value. For an object, such as an email item, you can click on the plus sign and the object will expand to reveal all of the properties. In general, you can access these properties by using obj.this.that(itemnumber). – Hrothgar Nov 24 '16 at 18:21
0

Thanks to both @hrothgar and @Tony for the responses. I learned about some new tools and techniques from each. Ultimately, based on the info found in your responses, I ended up searching for "vba get recipients string" and finding Get item.recipient with Outlook VBA .

The code now works and looks like this:

Private Sub CommandButton1_Click()

Dim origEmail As MailItem
Dim replyEmail As MailItem

'new stuff
Dim recip As Recipient
Dim allRecips As String
'end new stuff

Set origEmail = ActiveExplorer.Selection(1)
Set replyEmail = CreateItemFromTemplate("C:\Template Placel\My Template.oft")

'more new stuff
For Each recip In origEmail.Recipients
If (Len(allRecips) > 0) Then allRecips = allRecips & "; "
allRecips = allRecips & recip.Address
Next
'end more new stuff

replyEmail.To = origEmail.SenderEmailAddress & "; " & allRecips 'updated to find sender email and 

replyEmail.HTMLBody = replyEmail.HTMLBody & origEmail.Reply.HTMLBody
replyEmail.SentOnBehalfOfName = "inbox@company.com"
replyEmail.Display

Set origEmail = Nothing
Set replyEmail = Nothing

End Sub

The internal contacts look a bit ugly in the "To" line, but I've tested and it gets the job done.

Thanks for the help!

Community
  • 1
  • 1
JCB
  • 1
  • 5