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 handily 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 are:
' 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:
The resulting email has the name given in the .From parameter ("replytoname" here, and can be Mickey Mouse or any text string you want) but the associated email address is the Gmail address given in the CDO parm 'sendusername'. i.e. "email@example.com" is totally ignored everywhere. Same if it's set in the CDO parm "senduserreplyemailaddress" (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 and so cannot use a local SMTP server. I'm using my own Gmail account (which I only use on my iPad, and rarely there) 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. I posted this on another well-known Excel help forum but it received no responses at all. I'm hoping that this post might reach a different set of experts who will be able to shed the light. Or at least make some suggestions. Anyone know if Mr De Bruin is receptive to random questions directly from strangers?