Microsoft Office Tutorials and References
In Depth Information
Putting It All Together
To copy the list, declare a String variable for the text values as you loop through each cell in the
list, and declare Long variables for the count of rows and columns in the range you are copying. In
this example it is a single column being copied. However, the range you want to copy might have
an unknown number of rows and columns to be represented in your e-mail. The following example
takes into consideration a dynamic range based on the CurrentRegion property of cell A1:
Dim strtext As String
Dim xRow As Long, xColumn As Long
For xRow = 1 To Range(“A1”).CurrentRegion.Rows.Count
For xColumn = 1 To Range(“A1”).CurrentRegion.Columns.Count
strtext = strtext & “ “ & Range(“A1”).Cells(xRow, xColumn).Value
Next xColumn
strtext = strtext & Chr(10)
Next xRow
putting it All Together
The following macro ties together all the previous code examples in this lesson. Figure 28-2 shows
what your e-mail would look like in Outlook 2010 after running the macro named ExampleEmail .
Sub ExampleEmail()
‘Declare and establish the Object variables for Outlook.
Dim objOutlook As Object
Dim objNameSpace As Object
Dim objInbox As Object
Dim objMailItem As Object
Set objOutlook = CreateObject(“Outlook.Application”)
Set objNameSpace = objOutlook.GetNamespace(“MAPI”)
Set objInbox = objNameSpace.Folders(1)
Set objMailItem = objOutlook.CreateItem(0)
‘Declare a String variable for the worksheet data.
Dim strtext As String
‘Declare Long variables for the range’s Row and Columns.
Dim xRow As Long, xColumn As Long
‘Build the string that is the text inside the range
‘you want to represent in the Body of the email.
For xRow = 1 To Range(“A1”).CurrentRegion.Rows.Count
For xColumn = 1 To Range(“A1”).CurrentRegion.Columns.Count
strtext = strtext & “ “ & Range(“A1”).Cells(xRow, xColumn).Value
Next xColumn
strtext = strtext & Chr(10)
Next xRow
‘Activate or open Outlook.
On Error Resume Next
AppActivate (“Outlook”)
If Err.Number <> 0 Then objInbox.Display
Err.Clear
Search JabSto ::




Custom Search