| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Sending mail with CDO through a remote SMTP server
I want to send emails from VBA using the CDO functionality. I have used the method described by Ron De Bruin successfully in the past but only using my local SMTP server. I now need to do the same with a remote server, such as Gmail.
I found something potentially suitable in the ever-informative Ron de Bruin's downloaded examples (http://www.rondebruin.nl/files/CDO_Example_Code.zip) which gives sample code for using with Gmail. This works, and emails do get sent and are received. However, the sticking point for me is what goes into the From (or reply-to) field in the delivered email. The comments where he says you can use the .ReplyTo parameter before the .Send seem appropriate. Here they a ' Note: The reply address is not working if you use this Gmail example ' It will use your Gmail address automatic. But you can add this line ' to change the reply address .ReplyTo = " Sorry Ron, this doesn't work (at least for me). Here's my simplified and anonymousised code, which is virtually identical to RdB's code except I had to user port 465: Code:
Sub TestingGmail()
Dim iMsg As Object, iConf As Object
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1 ' CDO Source Defaults.
Set Flds = iConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "(gmailusers password)"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
' .Item("http://schemas.microsoft.com/cdo/configuration/senduserreplyemailaddress") = "
.Update
End With
With iMsg
Set .Configuration = iConf
.To = "
.CC = ""
.BCC = ""
.ReplyTo = "
.From = """replytoname"" )" ' Actual code has '<' and '', not '(' & ')'
.Subject = "Title"
.TextBody = "Text body"
.Send
End With
Set iMsg = Nothing
Set iConf = Nothing
End Sub
The resulting email has the name given in the .From parameter ("replytoname" here) but the associated email address is the Gmail address given in the CDO parm 'sendusername'. i.e. ' is totally ignored everywhere. Same if it's set in the senduserreplyemailaddress in the CDO parms (another potential solution to this that doesn't work). A 'Reply' to this email will go to the Gmail address, not to the sender's address. NOT what I want! I've googled this five ways from Sunday with no solution found and I'm out of ideas. Is there something in my PC's setup that might be preventing this working? I have Windows 7 Home and Office 2007. I use Windows Live for my normal email client (Outlook is not installed), but I'm writing this application for a friend who only uses Hotmail and also has no Outlook. He runs W7 and Office 2010. He may or may not have Windows Live installed but in any case he does not use it and it will have no accounts defined. I'm using my own Gmail account (which I only really use on my iPad) as the remote server for all this but if it doesn't work for me is it ever going to work for him? I need that return address to be his Hotmail address as he *will* be expecting replies. In this testing phase I'm using my own O2 account in its place, not that it matters a jot what that address is as it's ignored anyway. Help. Please. |
|
#2
|
|||
|
|||
|
Some of the code got lost in translation. Here it is again, properly in full:
Code:
Sub TestingGmail()
Dim iMsg As Object, iConf As Object
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
iConf.Load -1 ' CDO Source Defaults.
Set Flds = iConf.Fields
With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "(gmailusers password)"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
' .Item("http://schemas.microsoft.com/cdo/configuration/senduserreplyemailaddress") = "
.Update
End With
On Error Resume Next
With iMsg
Set .Configuration = iConf
.To = "
.CC = ""
.BCC = ""
.ReplyTo = "
.From = """replytoname"" "
.Subject = "Title"
.TextBody = "Text body"
.Send
End With
On Error GoTo 0
Set iMsg = Nothing
Set iConf = Nothing
End Sub
Bill |
|
#3
|
|||
|
|||
|
OK. Fixed it. I have to admit to a certain amount of redness in the facial area.
I didn't go far enough in the testing of the received email. The displayed heading in my Windows Mail Inbox of was of the form "replytoname )" and in my innocence I believed that email address to be the Reply To address. I never actually tried to do a reply to it. When I did that today, more out of frustration than serious testing, lo and behold it provided the correct email address in the To field. i.e. from my code above. Case closed. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Name of SMTP server | Excel Programming | |||
| To send an email to a remote SMTP server | Excel Programming | |||
| smtp server | Excel Programming | |||
| Problems with sending eMail by SMTP | Excel Programming | |||
| Sending mail from Excel with CDO - return SMTP value | Excel Programming | |||
Hybrid Mode
