Solved

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

Posted on 2010-09-01
19
753 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
ID: 33579715
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
ID: 33579823
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
ID: 33579915
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 65

Expert Comment

by:rockiroads
ID: 33579925
what version office r u using?
0
 
LVL 19

Author Comment

by:Eric Sherman
ID: 33580039
It's an Office 2000 application.

ET
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 33580222
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
ID: 33580396
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
ID: 33580556
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
ID: 33580835
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 33580970
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
ID: 33582853
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
ID: 33582897
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
ID: 33582901
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
ID: 33582907
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
ID: 33582912
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
ID: 33582916
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
ID: 33582917
okay no more spamming posts :) until you respond lol
0
 
LVL 19

Author Comment

by:Eric Sherman
ID: 33582926
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
ID: 33593470
Thanks rockiroads ....You really saved me a lot of time on this one.

ET
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes – formerly IBM Notes – is an email client application, while IBM Domino (earlier Lotus Domino) is an email server. The client possesses a set of features that are even more advanced as compared to that of Outlook. Likewise, IBM Domino is…
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 Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This video demonstrates how to sync Microsoft Exchange Public Folders with smartphones using CodeTwo Exchange Sync and Exchange ActiveSync. To learn more about CodeTwo Exchange Sync and download the free trial, go to: http://www.codetwo.com/excha…

786 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