VBA code to export cell contents into Lotus Notes Body

[jkrenkel]

I have the code to create an excel macro to Lotus notes, but am having a problem. I would like to take some data from my workbook and feed it into Notes.

I have it so it looks through each row to find the data, and compiles it just fine, into another excel file. I think it needs to compile the data into a temp file and them post it somehow to MailDoc.Body, but my brain is failing me.

Here is my loop code. Any idea how to get it to gather the data and put it in MailDoc.Body rather than the excel file I created.

I am assuming instead of the DestFile I need to create some sort of DestCache and then pull it out of there somehow.

' 

FileNum = FreeFile()
DestFile = "FILENAME"
   
On Error Resume Next

Open DestFile For Output As #FileNum

If Err <> 0 Then
MsgBox "Cannot open file "

End
End If

On Error GoTo 0

'OTHER CODE HERE

Loop for each row in selection.
For Each cell In Range("A1", Cells(65535, Range("a65536").End(xlToLeft).Column).End(xlUp))



' Write current cell's text to file with quotation marks.
'Add extra columns to this line with the & "," seperating them.
'Change value in zeropad() to match the cell width.

Print #FileNum, zeropad(cell.Offset(0, 0), 10) & "" & zeropad(cell.Offset(0, 1), 40) & "" & zeropad(cell.Offset(0, 2), 12) & "" & zeropad(cell.Offset(0, 3), 12)



Next cell

Close #FileNum

I hope this makes some sense[/code]

 

[Smitty]

Here's an example using text & cell references:

<font face=Tahoma>                <SPAN style="color:#007F00">'   Name and Message Body</SPAN>
                    FirstName = Right$(cell, (Len(cell) - InStr(1, cell, ",")))
                    Recipient = cell.Offset(0, 4).Value
                <SPAN style="color:#007F00">'   Indicate the following:</SPAN>
                <SPAN style="color:#007F00">'       Base Hours</SPAN>
                <SPAN style="color:#007F00">'       Friends & Family Hours</SPAN>
                <SPAN style="color:#007F00">'       Total Hours</SPAN>
                <SPAN style="color:#007F00">'       PennySaver Bucks Earned</SPAN>
                <SPAN style="color:#007F00">'       PennySaver Bucks Spent</SPAN>
                <SPAN style="color:#007F00">'       Account Balance</SPAN>
                <SPAN style="color:#007F00">'       Volunteer Club Status</SPAN>
                    MailDoc.Body = "Dear" & FirstName & "," _
                        & vbNewLine & vbNewLine & _
                    "You have volunteered for " & cell.Offset(0, 5).Value & " Hours this year." _
                        & vbNewLine & vbNewLine & _
                    "Your Friends and Family have volunteered for " & cell.Offset(0, 6).Value & " Hours this year." _
                        & vbNewLine & vbNewLine & _
                    "You have a total of " & cell.Offset(0, 7).Value & " Volunteer Hours this year." _
                        & vbNewLine & vbNewLine & _
                    "You have earned $" & cell.Offset(0, 8).Value & " PennySaver Bucks this year." _
                        & vbNewLine & vbNewLine & _
                    "You have spent $" & cell.Offset(0, 9).Value & " PennySaver Bucks this year." _
                        & vbNewLine & vbNewLine & _
                    "You have a balance of $" & cell.Offset(0, 10).Value & " PennySaver Bucks in your account." _
                        & vbNewLine & vbNewLine & ClubStatus _
                        & vbNewLine & vbNewLine & vbNewLine & vbNewLine & _
                    "This is an automated e-mail from the PennySaver Volunteer Management System..." _
                    & vbNewLine & "Please do not respond."</FONT>

HTH,

Smitty