Automatically create Gmail with text, based on date entry in Excel worksheet

**F1Fan** · 12-13-2012, 06:40 PM

Hi,

I'm trying to automatically create a Gmail, triggered by entering a date in an Excel workbook in column Q (17) and in rows 6 or greater. I have wording in the e-mail that I want to use and will change e-mail addresses in the VBA so it works for each of my users. We're using MS Office Excel 2003, but we'll be moving to 2010 shortly.

The following code works in creating an e-mail but it creates an Outlook e-mail instead of a Gmail:
***************
(Under Sheet1)

Please Login or Register  to view this content.

I haven't been able to find a thread that provides exactly what I need to solve my problem. I'm no expert in VBA, so posting code in your response would be greatly appreciated!! Thanks.

Moderator's Note: Welcome to the forum, you should put code tags around codes. Select the code then hit the "#" sign. I'll do it for now. Thanks.

**Leith Ross** · 12-15-2012, 12:00 AM

Hello FIFan,

The code below will send the email using your Gmail account. You will need to add your email address and password to the macro "Send_By_Gmail" before it will work. The "Send_By_Gmail" macro needs to copied into a seaprate VBA module in your workbook's VBA Project.

Worksheet_Change Event Code

Please Login or Register  to view this content.

Module Code

Please Login or Register  to view this content.

**F1Fan** · 12-15-2012, 10:57 AM

Thanks, Leith, for taking the time to re-construct my code!

I added your "Worksheet" code to Sheet1 under Microsoft Excel Objects under VBAProject and the second part under Module1. I entered my Gmail user name and password where you indicated, but when I enter a date in column Q on Sheet1, I get a cryptic Run-time error that includes "The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response wasn't available." When I click on the Debug button, the ".Send" line is highlighted in yellow.

I recall seeing a different post saying to try 25 instead of 465 as the "smtpserverport". but I get the same result. Does some other port number work or is there a different problem?

Also, I wasn't sure if I was supposed to replace "FileToAttach" with the location of full path of the Excel file, so I left that alone.

Finally, our company hasn't made the transition to using Chrome as our default browser; we're still using IE 7. Could that be part of the problem? I tried running the solution with and without Gmail open - not sure if that would make a difference, but it hasn't quite worked yet.

Any suggestions?

**Leith Ross** · 12-15-2012, 04:25 PM

Hello F1Fan,

The message you got is typical when something isn't right with the CDO code like a misspelled word or a missing value or punctuation. The error is probably related to the file you wanted to attach. I did not say it in my last post but you need to include the file's path.

Syntax Example
Send_By_Gmail SendTo:="[email protected]", Subject:="CDO Test Message", Message:="This is to test the CDO mail macro code.", FileToAttch:="C:\Documents and Settings\Owner\Workbook 1.xls"
NOTE: The strings in quotes above can be replaced by string variables.

Try that first. As for the port numbers 465 is used with SSL (Secure Socket Layer) connections. These require a user name and password. For example, my gmail account is "[email protected]". It is possible another problem may be if you are connecting through a network server. If so then some changes would need to be made.

Sincerely,,
Leith Ross

**F1Fan** · 12-16-2012, 10:10 AM

I get a compile error with your Syntax example. "SendTo" is highlighted with "Expected: end of statement".

**Leith Ross** · 12-16-2012, 03:38 PM

Hello F1Fan,

Check your punctuation. "SendTo" is a named argument. Named arguments have a special suffix which is := Be sure it has was added correctly to the arguments in your code. The arguments are SendTo, Message, Subject, CCto, BCCto, FileToAttach. When using named arguments, the order the arguments is in becomes irrelevant. If the arguments were not named then each argument would have to be provided in the correct order.

You did not say if you are using this on network. Are you?

**F1Fan** · 12-16-2012, 06:09 PM

Hi Leith,

I copied and pasted your code exactly as is (including changing/not changing "FileToAttch" to "FileToAttach") without updating the e-mail address and filename for what I need - I just to check the syntax at this point. However, I got same error message as first reported above (i.e., a cryptic run-time error along with "The message could not be sent to the SMTP server. The transport error code was 0x80040217. The server response was not available") Sorry, but I don't know enough VB to de-bug further.

I'm using VPN connected to our company's network to load Gmail. Do I need to ask something from my IT Department to avoid not getting the VBA error message?

Thanks again for spending the time helping me!

**Leith Ross** · 12-16-2012, 07:42 PM

Hello F1Fan,

Since you are on a VPN, it is probably using a newer IMAP4 server rather than the older IMAP. The secure IMAP server port number is 465. Trying changing this to 585, which is for secure IMAP4 server connections. I have made the change to the code below in red.

Please Login or Register  to view this content.

**F1Fan** · 12-17-2012, 12:17 PM

Hi Leith,

We're getting closer. I changed the smtpserverport to 585 as you suggested. Also, I replaced the FileToAttach value in the line starting with .AddAttachment to the full path to where the file is located where I'm entering the date. (I surrounded the path in quotes with backslashes, "D:\Documents and Settings\...\filename.xls").

When I enter a date, however, the error message shows a cryptic run-time error plus "The process cannot access the file because it is being used by another process." I don't want to attach the file; I just want to pull in the date value entered and insert it as part of the Gmail message when Gmail "composes" the e-mail message. I have the Worksheet_Change coding associated with Sheet1 where the date is entered, and the Send_By_Gmail coding in Module1; both sets of code are in the file on my D: drive referenced above.

For testing, I'm trying to send the Gmail to my private Gmail address from my business Gmail address.

Automatically create Gmail with text, based on date entry in Excel worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Automatically create Gmail with text, based on date entry in Excel worksheet

Re: Automatically create Gmail with text, based on date entry in Excel worksheet

Re: Automatically create Gmail with text, based on date entry in Excel worksheet

Re: Automatically create Gmail with text, based on date entry in Excel worksheet

Re: Automatically create Gmail with text, based on date entry in Excel worksheet

Re: Automatically create Gmail with text, based on date entry in Excel worksheet

Re: Automatically create Gmail with text, based on date entry in Excel worksheet

Re: Automatically create Gmail with text, based on date entry in Excel worksheet

Re: Automatically create Gmail with text, based on date entry in Excel worksheet

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions