Print array in text body of CDO email

Question

I have successfully loaded an array myArray(30,1) with data and would like to print the data in the text body of the CDO email.This is the section of code that works fine (it has been is service for months) before I attempt to print the array in the text body. You can see I was simply passing the variables Fullname and Amt:

 Set iMsg = CreateObject("CDO.Message")
                With iMsg
                    Set .Configuration = iConf


                    .To = "[email protected]"
                    .From = """ME Report Error"" <[email protected]>"
                    .Subject = "Current Period Monthly Report variance"
                    .TextBody = "Hello," & vbNewLine & vbNewLine & _
                                "Please review the variances to NAV in the following report:" & vbNewLine & vbNewLine & _
                                "Report Location:  " & FullName & vbNewLine & vbNewLine & _
                                "Variance amount:  " & Amt & vbNewLine
                    .Send
                End With
                Set iMsg = Nothing

Now when I attempt loop through my array in the text body I get an error:

 Set iMsg = CreateObject("CDO.Message")
                With iMsg
                    Set .Configuration = iConf


                    .To = "[email protected]"
                    .From = """ME Report Error"" <[email protected]>"
                    .Subject = "Current Period Monthly Report variance"
                    .TextBody = "Hello," & vbNewLine & vbNewLine & _
          ERROR  ------------ >  & For x = LBound(myArray, 1) To UBound(myArray, 1)
                                  For y = LBound(myArray, 2) To UBound(myArray, 2)
                                    ThisWorkbook.Sheets("Sheet1").Cells(x + 1, y + 1) = myArray(x, y)
                                Next y
                            Next x
                    .Send
                End With
                Set iMsg = Nothing

I know the syntax inside of the loops is incorrect. I used that to drop the array into a spreadsheet (which worked fine). But I was hoping a slight modification would allow me to put it into the email text body, however it is simply not liking me placing code there it seems. The error I am receiving is

"Compile Error: Expected: Expression"

The error falls on the & on the first line below the .TextBody line. I am not conviced this goal is even possible. I may have to print the array in a spreadsheet and email it as an attachment, but would much prefer to have it print in the text body.

Much appreciated!

Answer 1

Place outside of compiling body of email:

For x = LBound(myArray, 1) To UBound(myArray, 1)
      For y = LBound(myArray, 2) To UBound(myArray, 2)
        ThisWorkbook.Sheets("Sheet1").Cells(x + 1, y + 1) = myArray(x, y)
    Next y
Next x

Copy Range to Clipboard:

ThisWorkbook.Sheets("Sheet1").Range(ThisWorkbook.Sheets("Sheet1").Cells(1+LBound(myArray, 1),1+LBound(myArray, 2)),ThisWorkbook.Sheets("Sheet1").Cells(1+UBound(myArray, 1),1+UBound(myArray, 2))).Copy

Then you can get your data off the clipboard:

"Hello," & vbNewLine & vbNewLine & ClipBoard_GetData()

But first you need to place the function declaration at the top of your code outside of any function or sub:

Declare Function OpenClipboard Lib "User32" (ByVal hwnd As Long) _ 
   As Long 
Declare Function CloseClipboard Lib "User32" () As Long 
Declare Function GetClipboardData Lib "User32" (ByVal wFormat As _ 
   Long) As Long 
Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags&, ByVal _ 
   dwBytes As Long) As Long 
Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _ 
   As Long 
Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) _ 
   As Long 
Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) _ 
   As Long 
Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _ 
   ByVal lpString2 As Any) As Long 

Public Const GHND = &H42 
Public Const CF_TEXT = 1 
Public Const MAXSIZE = 4096

Function ClipBoard_GetData() As String
   Dim hClipMemory As Long 
   Dim lpClipMemory As Long 
   Dim MyString As String 
   Dim RetVal As Long 

   If OpenClipboard(0&) = 0 Then 
      MsgBox "Cannot open Clipboard. Another app. may have it open" 
      Exit Function 
   End If 

   ' Obtain the handle to the global memory 
   ' block that is referencing the text. 
   hClipMemory = GetClipboardData(CF_TEXT) 
   If IsNull(hClipMemory) Then 
      MsgBox "Could not allocate memory" 
      GoTo OutOfHere 
   End If 

   ' Lock Clipboard memory so we can reference 
   ' the actual data string. 
   lpClipMemory = GlobalLock(hClipMemory) 

   If Not IsNull(lpClipMemory) Then 
      MyString = Space$(MAXSIZE) 
      RetVal = lstrcpy(MyString, lpClipMemory) 
      RetVal = GlobalUnlock(hClipMemory) 

      ' Peel off the null terminating character. 
      MyString = Mid(MyString, 1, InStr(1, MyString, Chr$(0), 0) - 1) 
   Else 
      MsgBox "Could not lock memory to copy string from." 
   End If 

OutOfHere: 

   RetVal = CloseClipboard() 
   ClipBoard_GetData = MyString

End Function