PDA

View Full Version : Write excel cell value into new email



Stephanie
05-23-2006, 10:32 AM
example - I would like the value of cell A1 from an excel worksheet to write into my the body of my email.

How would I go about this?

This is the code I have so far. (Found on another thread and slightly modified.)



Sub SendMailMessage()
Dim objOLapp As Object, obXLapp As Object
Dim objMailItem As Object
Dim strBody As String
Dim un As String
Set obXLapp = CreateObject("Excel.Application")
Set objOLapp = CreateObject("Outlook.Application")
Set objMailItem = objOLapp.CreateItem(0) '0=mailitem
un = Environ("USERNAME")

With obXLapp
.Visible = True
.Workbooks.Open ("D:\Documents and Settings\" & un & "\My Documents\MyExcelFile.xls")
End With

' strBody = range("A1").value ???

With objMailItem
.Body = strBody
End With

End Sub

Norie
05-23-2006, 10:49 AM
Stephanie

That's almost right.

Sub SendMailMessage()
Dim objOLapp As Object, obXLapp As Object
Dim obXLWB As Object
Dim objMailItem As Object
Dim strBody As String
Dim un As String
Set obXLapp = CreateObject("Excel.Application")
Set objOLapp = CreateObject("Outlook.Application")
Set objMailItem = objOLapp.CreateItem(0) '0=mailitem
un = Environ("USERNAME")

With obXLapp
.Visible = True
Set obXLWB = .Workbooks.Open("D:\Documents and Settings\" & un & "\My Documents\MyExcelFile.xls")
End With

strBody = obXLWB.ActiveSheet.Range("A1").Value

With objMailItem
.Body = strBody
End With

End Sub

Stephanie
05-23-2006, 11:48 AM
Thanks.

Stephanie
05-23-2006, 12:36 PM
I have outlook set to attach my signature to all new, fowarded and replied messages. When I create a new email using the above code my signature is not included. How do I include it?