This is probably basic VB programming question, and if so the thread can be deleted after pointing me in the correct direction (hopefully), but I'm looking to create a macro that when clicked, would prompt the user for several pieces of information (project number, customer name, price), and then insert them into a pre-written email (perhaps a quick text insert), and also insert those values into an excel block that has been pulled into the email.

If it would also give the option to upload a file and attach that to the email, that would be nice.

I send so many of these emails, it would save me some time, and just be convenient to have them consistent.

5/19/2009 10:20:46 AM

i don't know that this sounds THAT simple

5/19/2009 11:55:49 AM

i think the VBA would be written in Excel, then use Excel to send the emails to Outlook. Not Outlook sending the data to Excel.
http://www.rondebruin.nl/sendmail.htm

5/19/2009 11:58:27 AM

Do you want an embedded excel object, or an attached excel worksheet?

Both aren't terribly hard, but they are two different implementations.

You can automate across office applications very easily. From Outlook, you just need to do something like:

Set myExcel = CreateObject("Excel.Application")
Set wkbk = myExcel.Workbooks.Add
myExcel.Worksheets("Sheet1").Activate

myExcel.ActiveSheet.(INSERT YOUR EXCEL SPECIFIC VBA HERE)

Then save it, attach it and delete the local copy (with VBA) from outlook.

[Edited on May 19, 2009 at 2:39 PM. Reason : .]

5/19/2009 2:39:14 PM

How much is this worth to you?

I've done something similar for somebody for time sheets.. prompts for hours, puts them into an excel thing, emails it out. there are ways to email through outlook or without outlook.

5/19/2009 4:09:51 PM

Set objEmail = CreateObject("CDO.Message")

objEmail.From = "helpdesk@fabrikam.com"
objEmail.To = "administrator@fabrikam.com"
objEmail.Subject = "Server down"
objEmail.Textbody = "Server1 is no longer accessible over the network."
objEmail.AddAttachment "C:\Scripts\Output.txt"

objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
"smtpmailer"
objEmail.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update

objEmail.Send

5/19/2009 4:15:31 PM

There was a time I could probably have done half of that, but those skills are covered in about 2 lbs of dust

5/19/2009 4:15:31 PM

^^^^ Late binding precludes the use of intellisense. Add a reference to Microsoft Office object library and use early binding.

Creating any email in Outlook using VBA is going to bring up a security alert. It may be possible to get rid of the alert but I wouldn't recommend it as the reduced security would apply to potentially malicious VBA from outside sources.

Instead create a custom form for sending email in outlook and run the code from a macro in the form. (Note: An outlook form, not a VBA form.)

[Edited on May 19, 2009 at 4:20 PM. Reason : ]

5/19/2009 4:20:05 PM