command button VBA to send automated email not workbook or worksheet

**lpratt** · 02-28-2012, 07:29 PM

Has anyone got or seen a code that can send a automated email via outlook.

i do not want to send the workbook or worksheet but just to be able to send a text only email when command button is pushed?

**lpratt** · 02-28-2012, 07:50 PM

the recipient would always be the same eg.
"joe@comuter.com"

Subject would always be the same eg.
"SAP information required"

and the body of text would always be the same
"Please provide the following information:

Sold To:
Ship To:
Bill To:
Required Delivery Date:
Comments:
"

**smuzoen** · 02-28-2012, 07:54 PM

Just search the forum for "Excel Outlook". There are so many threads posted here you will get the answer. As general answer

Please Login or Register  to view this content.

That is the main "guts" of the email from excel code.

**smuzoen** · 02-28-2012, 08:03 PM

Add a button to a worksheet - assign a macro to the button - in this case macro called sndEmail

Please Login or Register  to view this content.

**lpratt** · 02-28-2012, 08:30 PM

wonthat is wonderful thank you

what if i wanted to change information eg.

"Please provide the following information for equipment number: (cell reference eg. =k10)

**smuzoen** · 02-29-2012, 02:31 AM

The body of the email is made up of the following string

Please Login or Register  to view this content.

vbCrLf adds a carriage return/line feed so that will start a new line. If K10 contains a reference to the Equipment Number then

Please Login or Register  to view this content.

**lpratt** · 03-08-2012, 10:39 PM

i cant seem to get this to work

how about changing the first line of the body of text

"Please provide the following information for equipment :" then insert number from k10

and also the subject line to

.Subject = "SAP information required" then insert number from k10

**smuzoen** · 03-08-2012, 11:13 PM

i cant seem to get this to work

What do you mean? Is their no value for K10? If the value for K10 is on e.g. sheet1 then try

Please Login or Register  to view this content.

The same for the subject

Please Login or Register  to view this content.

Make sure you reference the sheet containing the value for K10 (substitute Sheet1 for whatever the sheet is called)
Try this - if that does not work then simply upload a sample workbook with no sensitive data and I will fix the code - to place the value in K10 into the email is not difficult to do - it is probably referencing issue - without a sample workbook it is difficult to tell. Just upload the workbook with the sheets containing the button and the sheet with the value for K10 - if the button and the value for K10 are on different sheets then referencing will be your problem. This should be easy to fix rather than manually entering the value from K10!