Avatar of Dale Massicotte
Dale Massicotte
Flag for United States of America asked on

Changed from EXCHANGE SERVER to Google APPS /// Sending EMAIL with CDO.Configuation code VBA -

Why wont this code work.  I changed the smtp server from our local ip to aspmx.l.google.com as we are now using google apps instead of exchange server.


Dim iCfg As Object
    Dim iMsg As Object
 
    Set iCfg = CreateObject("CDO.Configuration")
    Set iMsg = CreateObject("CDO.Message")
   
    With iCfg.Fields
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver")    = "aspmx.l.google.com"
        '.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        '.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "GxxxxxCxxxxxr"
        '.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxx9xx"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress") = "dispatch@premierexpress.net"
        .Update
    End With
 
 
    With iMsg
        .Configuration = iCfg
        .subject = "WB:" & WaybillNumber & " READY:  " & txtReadyPrompt
        .To = ptn & sfx  '"dalerwhaler@yahoo.com"
        '.To = ptn & "@txt.att.net"  '"dalerwhaler@yahoo.com"
        '.CC = "cc@email.com"
        '.BCC = "bcc@email.com"
        .FROM = "dispatch@premierexpress.net"        '.FROM = "from@email.com"
 
        'If StrComp(Left(strMessageBody, 6), "<HTML>", vbTextCompare) = 0 Then
        '    .HTMLBody = "HTML TEXT"
        'Else
            .TextBody = FormDetail
        'End If
       
        'Add any specified attachments
        '.AddAttachment "Attachment path"
 
        .send
    End With

This code will not work.  Note that I commented out these lines

        '.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        '.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "GxxxxxCxxxxxr"
        '.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxx9xx"


The error message I get is:

The server rejected one or more recipient email addresses.  The server response was 550 5.1.1
http://support.google.com/mail/bin/answer.py?answer=6596
gg6si2491634qab.73
ExchangeMicrosoft Access

Avatar of undefined
Last Comment
Dale Massicotte

8/22/2022 - Mon
cnpz

aspmx.l.google.com doesn't route externally. You can send to any Google Apps or Gmail address, but not the outside world. smtp.gmail.com is the one for that (but has its issues - see below).

Was going to say you don't need to authenticate with aspmx.l.google.com, but that's already there.


The problem is I'm not sure CDO can do smtp over SSL, which is what you'd need for smtp.gmail.com. If it can't, your options are

(1) tunnel, eg stunnel, so you can send to plain smtp and have it encode for you
(2) use a mail client (eg Outlook) and send through that
(3) using a library that supports SSL

For stunnel, all you'd need in the config file is

[smtpgmail]
accept = 127.0.0.1:25
connect = smtp.gmail.com:465

Then point your code to the machine running stunnel on port 25, and use login credentials with user@yourdomain.com and the gmail password.


EDIT: Looks like CDO can do SSL after all? http://msdn.microsoft.com/en-us/library/ms526975(v=exchg.10) In which case, try using stmp.gmail.com on port 465, and then  587 if that doesn't work, with smtpusessl set to 1/true? With authentication.
Dale Massicotte

ASKER
replacing aspmx.l.google.com with smtp.gmail.com in my code does not work.

The code is executed by a button to send message.

How do I create and point a stunnel config file?  I just want to get this code to work as it is very urgent and my business is dependent on it
SOLUTION
ckratsch

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
cnpz

That's what you'd need to do to the first bit of your code. Might need to add a timeout, as per ckratsch's link.

Dim iCfg As Object
    Dim iMsg As Object
 
    Set iCfg = CreateObject("CDO.Configuration")
    Set iMsg = CreateObject("CDO.Message")
    
    With iCfg.Fields
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = true
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver")    = "smtp.gmail.com" 
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "realuser@yourgoogleappsdomain.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "realpassword"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendemailaddress") = "dispatch@premierexpress.net"
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
        .Update
    End With

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Dale Massicotte

ASKER
You guys are wonderful...thanks so far the help is working.
I tried the code as above and substituted my username password

There is one caveat :

When I receive the email it says it is from

realuser@yourgoogleappsdomain.com   --->
instead of the intended
dispatch@premierexpress.net

why would that happen?

I want it to be from dispatch@premierexpress.net so that the recipient can replay back to the entire distribution group "dispatch" instead of the value in sendusername
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Dale Massicotte

ASKER
cnpz your walk through was incredible

EVERYTHING works!