• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 779
  • Last Modified:

Access App Send Emails to Gmail instead of Outlook???

I have a Access 2k application that emails various report to customers with a PDF attachment using Outlook automation.  The client currently uses Exhange Server and the VBA in the front-end application is the pretty standard code to create a outlook text message, passes it to Redemption Outlook (to bypass Outlook security prompts, etc.) and drops the email in the users Outlook Outboox.

Now the client is thinking of using Google Apps and Gmail rather than Outlook and the Exchange Server.  I'm not that familiar with Google Apps right now and was wondering if this was possible because I'm thinking the Access 2000 application is not going to communicate with Google Apps.  Seems like this environment is looking for .NET or JAVA languages.  

Has anyone been able to create a VBA routine that will basically send an email from a Access application directly to a Google Apps Gmail account??

Any help, ideas, comments will be appreciated.

Thanks,

ET
0
Eric Sherman
Asked:
Eric Sherman
  • 11
  • 8
1 Solution
 
rockiroadsCommented:
gmail has pop3/smtp settings that you can use and configure in outlook. if you didn't have exchange could you still use your outlook automation to send from outlook but using the gmail settings? I am not sure about this, it would be something you need to test. Certainly I think you have to manually set your default email settings

alternative and probably quicker is to send email by using something like CDO where you can specify the smtp address http://www.freevbcode.com/ShowCode.asp?ID=7504
0
 
Eric ShermanAccountant/DeveloperAuthor Commented:
Thanks for the quick reply rockiroads .... longtime!!!

From what I can tell, the clients wants to completely do away wih Outlook and the Exchange Server.  I'm just trying to figure out if there is a way to compile the same text email but instead of sending it through Outlook prehaps using a HTTPS Post just send it directly to Gmail.  

I will look at the CDO methods.

ET
0
 
rockiroadsCommented:
hey ET yea man been away from EE too long. Needed the break lol

Would be better to use code and I think post would put details on the url. This would mean a login session has to be up? I suggested CDO because you can use it easily in vba. Plenty of examples here within EE also.

0
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
rockiroadsCommented:
what version office r u using?
0
 
Eric ShermanAccountant/DeveloperAuthor Commented:
It's an Office 2000 application.

ET
0
 
rockiroadsCommented:
lol yea, its in the question, doh!

okay, Ive got office 2007. I took the code I had in office 2003. I assume this would work in Office 2000.


Public Function TestSendByGmail()

    Dim objEmail As Object
    Dim objConf As Object
    Dim objFlds As Object
     
    On error resume next

    Set objEmail = CreateObject("CDO.Message")
    Set objConf = CreateObject("CDO.Configuration")
    Set objFlds = objConf.Fields
   
    objFlds.item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    objFlds.item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
    objFlds.item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
    objFlds.item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    objFlds.item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "youremailaddress to login gmail"
    objFlds.item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "yourpassword to login gmail"
    objFlds.item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1
    objFlds.Update
   
    objEmail.From = "your email address"
    objEmail.To = "who you are sending to"
    objEmail.Subject = "Test Send By Gmail"
    objEmail.TextBody = "Hello"
    objEmail.Sender = "Fred"
    objEmail.Configuration = objConf
    objEmail.Send
    If err.Number <> 0 Then
        MsgBox "Error in sending. " & err.Description
    Else
        MsgBox "Sent"
    End If
   
    Set objFlds = Nothing
    Set objConf = Nothing
    Set objEmail = Nothing
   
End Function

0
 
Eric ShermanAccountant/DeveloperAuthor Commented:
WOW!!!  Thanks rockiroads .... that gives me a clear understanding using the VBA route.  I was reviewing Googl Apps API and it was mostly referencing .NET and JAVA both of which are not in that Office 2000 environment.

1.) I've always used Outlook automation and not all that familiar with CDO (although it appears pretty straightforward).  I assume I will need to set some type of VB Reference to the CDO Library, correct???

2.) For attachments I can add this line???
objEmail.AddAttachment ("C:\MyFolder\MyFile.pdf")

You are wonderful dude and I really appreciate the help.  As the saying goes ...
"A picture is worth a thousand words!!!"

Thanks

ET


0
 
rockiroadsCommented:
hey thanks :)

I used late binding which means I created the object so there is no need to include cdo as a reference.
You can however do that then use cdo objects so the methods and properties will appear for you after you type in dot

and regarding attachments, your right though you don't really need the brackets.

I like CDO because it can be used for different email providers that offer smtp. I was not sure if it was still supported in office 2007 which is why I asked but it seemed to be working on mine.

One thing to remember though is requirements for cdo. I think you will be alright since you still have office. Not 100% sure if it needs exchange. I ran mine on my pc, no exchange though it is a company laptop (not connected).
0
 
Eric ShermanAccountant/DeveloperAuthor Commented:
Much appreciated!!!

I will begin testing in the morning and let you know.  

I wonder if I will need a Premium account at Google Apps to use smtp or will the Standard User account work???

ET
0
 
rockiroadsCommented:
I have a gmail account. One I signed up for on the web and that worked fine.
0
 
Eric ShermanAccountant/DeveloperAuthor Commented:
Thanks ...

I tested your code and it works great ... no problems... exactly what I was looking for.

However, I've run into another problem and I may have to open another question if need be.  The above will work when sending reports to customers because I will use a recordset processing loop for the customer reports, convert them to pdf, attach to the email with a saved message and send.  No problem there.

The problem is, I have another reports menu in the same application that's mainly used to distribute internal reports using the SendObject method where the Edit message paramater is set to True.  This creates the message but opens it in Outlook where the user can complete it by adding the To:, Subject: and Body fields, etc.  These reports will be changed from Snapshot to PDF as well but I'm just trying to figure out what's the best way to allow the user to edit the message before it's actually sent using the gmail smtp server or if this is even possible.

Any ideas will be appreciated.

Thanks,

ET

0
 
rockiroadsCommented:
You could create your own form whereby the user can enter the email info To, Subject, CC etc and have a file selection button to add attachments.
Then use this info to create the email ?
0
 
Eric ShermanAccountant/DeveloperAuthor Commented:
By the way ...

My thinking right now is to build a table in the Access app to store the gmail addresses then build a form that opens before the email is created allowing the user to select the email address(es) and enter a subject and body to compose the email.

Just my thoughts but maybe someone has another method of accomplishing this.

ET
0
 
rockiroadsCommented:
Your essentially creating a bespoke app here. Have you had a look at thunderbird? http://www.mozillamessaging.com/en-US/thunderbird/
0
 
rockiroadsCommented:
why reinvent when you have a freebie email client.
only other thing now is to handle your reporting. so maybe run your processing then call thunderbird compose mail.
this is what I thought so I checked thunderbird forum and found this, this might do the trick for you http://forums.mozillazine.org/viewtopic.php?t=399230&
0
 
rockiroadsCommented:
hey Im glad you asked this question. I am going to give thunderbird with vba a try later on. I tried thunderbird yonks ago but much prefer webmail.
0
 
rockiroadsCommented:
okay no more spamming posts :) until you respond lol
0
 
Eric ShermanAccountant/DeveloperAuthor Commented:
Thanks rockiroads ....

That's kind of what I was thinking (prompt the user via a form) then compose the email.  Just didn't know if someone had a better idea.

ET
0
 
Eric ShermanAccountant/DeveloperAuthor Commented:
Thanks rockiroads ....You really saved me a lot of time on this one.

ET
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now