Solved

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

Posted on 2010-09-01
19
748 Views
Last Modified: 2012-05-10
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
Comment
Question by:Eric Sherman
  • 11
  • 8
19 Comments
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 19

Author Comment

by:Eric Sherman
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
what version office r u using?
0
 
LVL 19

Author Comment

by:Eric Sherman
Comment Utility
It's an Office 2000 application.

ET
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
Comment Utility
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
 
LVL 19

Author Comment

by:Eric Sherman
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 19

Author Comment

by:Eric Sherman
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
I have a gmail account. One I signed up for on the web and that worked fine.
0
 
LVL 19

Author Comment

by:Eric Sherman
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 19

Author Comment

by:Eric Sherman
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Your essentially creating a bespoke app here. Have you had a look at thunderbird? http://www.mozillamessaging.com/en-US/thunderbird/
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
okay no more spamming posts :) until you respond lol
0
 
LVL 19

Author Comment

by:Eric Sherman
Comment Utility
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
 
LVL 19

Author Closing Comment

by:Eric Sherman
Comment Utility
Thanks rockiroads ....You really saved me a lot of time on this one.

ET
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Utilizing an array to gracefully append to a list of EmailAddresses
Learn to move / copy / export exchange contacts to iPhone without using any software. Also see the issues in configuration of exchange with iPhone to migrate contacts.
In this Micro Video tutorial you will learn the basics about Database Availability Groups and How to configure one using a live Exchange Server Environment. The video tutorial explains the basics of the Exchange server Database Availability grou…
This video discusses moving either the default database or any database to a new volume.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now