Creating mail/merge but using yahoo or gmail instead of the Outlook default.

[Daccess]

I can create an excel file of the data I want to use for a mail merge email but I always get Outlook as the mail server. Is it possible to use yahoo or gmail instead of outlook? Word mail merge does the same thing.

[Micron]

Is it possible to use yahoo or gmail instead of outlook?

AFAIK, if you use CDO to send mail you don't need an email program at all because you would be interacting directly with the mail server. Whether or not those providers would give you the information you need or allow you to use their servers in that manner would be another thing, and I doubt it. If you are dealing with a workplace issue, you probably have access to the company server. I'm thinking you might be asking your question because you're thinking along the lines of email applications/providers rather than the server machines themselves.

[Paul H]

Just found this post. I am facing a similar problem. Several of our Access databases used Outlook to send out reports. This worked fine, but we have switched recently from Outlook to using GMail. Is there a way of using GMail for the same purposes? I've done a little research, but haven't found a practical solution.

Thanks,

Paul

[Gicu]

Yes, you can use GMail from Access (via CDO), there are many examples around.
http://learnexcelmacro.com/wp/2011/1...mail-or-yahoo/
https://blueclawdatabase.com/tutoria...ng-access-vba/

Cheers,

[Paul H]

Thanks for the quick response. I created a new topic for this question, just in case this didn't get a response. I'll take it down till I have the chance to look into this.

[isladogs]

If you want to try out the use of CDO, have a look at my free CDO EMail.Tester utility http://www.mendipdatasystems.co.uk/e...ter/4594365455

[Paul H]

Not sure if it makes any difference, but we are using GSuite, which uses Gmail to handle our corporate email. I am not just trying to use an outside email address and server to send out an email.

I am trying the code found at learnexcelmacro.com. It runs fine till I reach newmail.send where I get this error.

The transport failed to connect to Server

Sub SendEmailUsingGmail()

    On Error GoTo Err


    Dim NewMail As Object
    Dim mailConfig As Object
    Dim fields As Variant
    Dim msConfigURL As String


    Set NewMail = CreateObject("CDO.Message")
    Set mailConfig = CreateObject("CDO.Configuration")


    ' load all default configurations
    mailConfig.Load -1


    Set fields = mailConfig.fields


'Set All Email Properties


    With NewMail
        .Subject = "Test Mail from LearnExcelMacro.com"
        .From = "paul.h@dbhds.gov"
        .To = "paul.h@hotmail.com"
        .CC = ""
        .BCC = ""
        .textbody = "my test gmail report"
    End With


    msConfigURL = "http://schemas.microsoft.com/cdo/configuration"


    With fields
        'Enable SSL Authentication
        .Item(msConfigURL & "/smtpusessl") = True


        'Make SMTP authentication Enabled=true (1)
        .Item(msConfigURL & "/smtpauthenticate") = 1


        'Set the SMTP server and port Details
        'To get these details you can get on Settings Page of your Gmail Account
        .Item(msConfigURL & "/smtpserver") = "smtp.gmail.com"
        .Item(msConfigURL & "/smtpserverport") = 465
        .Item(msConfigURL & "/sendusing") = 2


        'Set your credentials of your Gmail Account
        .Item(msConfigURL & "/sendusername") = "paul.h@dbhds.gov"
        .Item(msConfigURL & "/sendpassword") = "Password!11"


        'Update the configuration fields
        .Update


    End With
    NewMail.Configuration = mailConfig
    NewMail.Send
    MsgBox ("Mail has been Sent")


Exit_Err:


    Set NewMail = Nothing
    Set mailConfig = Nothing
    End


Err:
    Select Case Err.Number


    Case -2147220973  'Could be because of Internet Connection
        MsgBox " Could be no Internet Connection !!  -- " & Err.Description


    Case -2147220975  'Incorrect credentials User ID or password
        MsgBox "Incorrect Credentials !!  -- " & Err.Description


    Case Else   'Rest other errors
        MsgBox "Error occured while sending the email !!  -- " & Err.Description
    End Select


    Resume Exit_Err


End Sub

[isladogs]

My CDO EMail Tester includes a help file which includes all common errors such as yours and what you need to do to fix them

[Paul H]

Micron,

I'm wondering if I am overworking this. This is definitely a workplace issue. Do I even have to deal with GMail? I have found the email server name and it doesn't even reference GMail, though I'm sure that must be done in the background. So far the suggestions so far have lead me to a dead end. I continue to get "The transport failed to connect to the server." My IT people are suggesting a more sophisticated solution using SSIS which I am hoping to avoid since I know nothing about it.


isladogs,

None of the suggestions in your help document seemed to help. I'll check back here for responses on Monday.

Paul

[isladogs]

I've used CDO successfully with all sorts of email accounts including GMail.
There are three possible causes of that particular error one of which applies specifically to GMail because of its new security permissions.
Did you read the additional detail on page 4 of that document?

With CDO you can use any valid email account but all the email 'processing' is done in Access.
There is no need to open an email client application such as GMail (or Outlook, Thunderbird , Groupwise etc etc) to use it.

[Micron]

Micron, I have found the email server name and it doesn't even reference GMail, though I'm sure that must be done in the background. So far the suggestions so far have lead me to a dead end. I continue to get "The transport failed to connect to the server." My IT people are suggesting a more sophisticated solution using SSIS which I am hoping to avoid since I know nothing about it.
isladogs,
Paul

I did get that error in my initial attempts. IIRC I was only able to solve it by distinguishing between sending and receiving servers by looking at email headers. I'm not surprised that GMail is not part of the identification of either. Not being an email expert I would characterize that as being like a mask or alias name for the server.

Part of my code looked like

Set objConfig = CreateObject("CDO.Configuration")
    objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cintCDOSendUsingPort
    objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = cstrSMTPServer
    objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    'objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 2
    objConfig.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 0

The red parts are constants in the declarations section. Obviously you need to get the port and server name correct. As I mentioned before, whether or not a provider would block attempts to use their servers remotely is something you'd have to find out from them. I think if you don't know the answer to that, you might end up trying 100 configurations without ever having a chance at success. If you don't want to ask them directly, try Googling about using CDO and Gmail for a start. In my case, the server was simply the same one my group was connected to when sending normal email via Outlook. I just bypassed Outlook altogether.
Hope that helps somewhat.