Microsoft Office Tutorials and References
In Depth Information
Transferring an Excel Range to the Body of Your E‑mail
Dim objInbox As Object
Set objOutlook = CreateObject(“Outlook.Application”)
Set objNameSpace = objOutlook.GetNamespace(“MAPI”)
Set objInbox = objNameSpace.Folders(1)
‘Activate Outlook if it is already open, or display Outlook’s
‘application object interface if it is closed.
On Error Resume Next
AppActivate (“Outlook”)
If Err.Number <> 0 Then objInbox.Display
Err.Clear
Dim objMailItem As Object
Set objMailItem = objOutlook.CreateItem(1)
With objMailItem
.To = “someone@somewhere.com”
.CC = “anyone@anywhere.com”
.Subject = “Testing my email code”
.Importance = 1 ‘Sets it as Normal importance (Low = 0 and High = 2)
.Body = “Hello, this is a test. Have a nice day.”
.Attachments.Add “C:\Your\File\Path\YourFileName.xlsx”
.Display ‘Change to Send if you don’t want to review the email before sending.
End With
End Sub
A couple of FYIs: The Importance property is optional; you don’t need to
include it. If you do include it as I did with this example, the “1” is a reference
to Normal Importance. Low Importance would be “0” and High Importance
would be “2.” Also, in all the e-mail examples in this lesson, “Display” is
utilized rather than “Send,” so that when you test these examples, you can actually
see the resulting MailItem object.
Transferring an Excel range to the Body of your E-mail
In the preceding example of composing a MailItem object, the body
of the e-mail message was hard-entered into the macro, with this
statement:
.Body = “Hello, this is a test. Have a nice day.”
You might be interested to know that you can represent a range of
worksheet data in the body of an e-mail message. One way to
accomplish that is to loop through the cells and create a text string, with a
line break character to simulate each row item. Figure 28-1 shows a
simple list that will be referred to in this example.
figurE 28-1
 
Search JabSto ::




Custom Search