Email: Reply To Address from Excel VBA

[UKSteveH]

I am currently trying to automate a receipt system. I have successfully managed to create an email based on calculated Excel data. The email is created using the following routine.

callref="12345"
strbody = "The main text of the email"

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "John Smith"
.BCC = ""
.Subject = "Test email receipt - " & callref
.Body = strbody
.Display 'or .Send - still developing!!!
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

My problem is that there will be several people using this system so I would like to include a reply address which is different from the sender. Alternatively, I would like to send the email from a different account (but I have to assume that the users will have their own email accounts open).

Any suggestions gratefully received.

 

[DonkeyOte]

Ron de Bruin has some excellent examples of mailing methods in VBA: http://www.rondebruin.nl/sendmail.htm

If you revert to CDO method you can define the From address: http://www.rondebruin.nl/cdo.htm
(an added bonus to the other more traditional advantage of no security warnings)

 

[UKSteveH]

Thanks for that.

The CDO method is probably not an option as it requires an SMTP server, (which I could find), but there is then a danger that the Exchange Server here (or more likely the anti-spam software) would block the emails.

Ron's examples are indeed excellent, but I cannot find any reference to a reply to field - I have already tried guessing .Reply .ReplyTo. I have tried the .Display method and then manually added a reply to address in the created email (and that works). My logic therefore suggests that there should be a simple solution to this this. But I cannot find it....

Unless you can think of anything else I will have a look on some Outlook forums to see if there is any clues there.

 

[DonkeyOte]

I don't know which Office suite you're running but see Ron's link re: XL/OL2007: http://www.rondebruin.nl/mail/account.htm

I have to say I would/have nearly always opted CDO myself... partly for this reason (ie using web apps to generate mail etc...)

(P.S. I didn't notice you were in Ipswich before... I'm about 8 miles outside the town limits )

 

[UKSteveH]

I updated my location when I logged in. I work by the football ground, but live in Kesgrave.

I am again looking at the CDO method using an SMTP program on the local machine (don't know yet if this will work!). This would solve many problems.

Steve

 

[RoryA]

If your users have the correct delegate permissions to the account, you can use the Sentonbehalfofname to send from another account. Otherwise I believe you want the ReplyRecipients collection.

 

[DonkeyOte]

Thanks Rory, I wasn't aware of that property.

Do you use Redemption ? I've never really been able to get my head around it and have always opted for CDO approach, just curious.

 

[RoryA]

I have done occasionally, though not for a while. It's certainly a lot easier to work with than Extended MAPI!!

 

[UKSteveH]

I cannot find any useful information about ReplyRecipients collection, and would I be able to use this inside the Excel VBA as the only info I found suggested that Outlook would block it.

 

[DonkeyOte]

When you say block it you mean the Security Prompt ?

You would get the Security alert with any VBA which tried to generate mail through OL unless you used Redemption and/or 3rd Party Tool like ClickYes ... this is why most would opt for CDO.

(I think the Security came in with 2003 SP2 but I can't recall exactly, Rory (aka The Oracle) will know )