PDA

View Full Version : Excel to Outlook



brennaboy
08-11-2010, 05:10 AM
Hi,

I believe that it is possible to produce a macro that will, for example - when you click on a buttom in an Excel Spreadsheet pull information from certain cells, open up an E-Mail message in out look and then format a message with that data.

E.G.

Takes Cells A1, A5, & F5

Opens new E-Mail message to "Customer Service Team"

Please can you look into the following transactions:

A1
A5
F5

Can anyone tell me if this is possible and if so start pointing me in the right direction?

Many thanks,

B.

MarkNumskull
08-11-2010, 05:18 AM
Hi There,

Give the Below a go. Just remember to add a variable to each of the cells

eg Dim sFirstCell as String

sFirstCell = Activesheet.range("a1").Value

You will need to replace all my variable names with your own apart from those with a * after, keep them

Dim sEmail As String, sCostCode As String, sDes As String, sIssue As String
Dim mlNewMessage *
Dim myOlApp *
Dim strBody As String *
sEmail = TextBox1.Text
sCostCode = CostCode1.TextBox1.Text
sDes = CostCode1.TextBox2.Text

sIssue = sCostCode & vbNewLine & sDes


MsgBox ("Your change has been logged and you will now receive an email confirmation along with your reference number")

Set myOlApp = CreateObject("Outlook.Application") 'Create New Outlook Item
Set mlNewMessage = myOlApp.CreateItem(olMailItem) 'Create New Mail Item
mlNewMessage.Body = ("Thank you for logging a change to the Authorisation List" & vbNewLine & vbNewLine & _
"The details are as follows." & vbNewLine & vbNewLine & _
"CostCode/Division - " & sCostCode & vbNewLine & vbNewLine & _
"Description - " & sDes) ' this is message body, amend as required
mlNewMessage.Subject = "Authorisation Change Reference: " & sRef1 'Defines email "Subject"
mlNewMessage.To = sEmail 'enter the email address in here
mlNewMessage.Send

Hope it helps!

Mark