VBA code to export cell contents into Lotus Notes Body

jkrenkel

New Member
Joined
Apr 25, 2006
Messages
23
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.


Code:
' 

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]
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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
 

Forum statistics

Threads
1,136,370
Messages
5,675,369
Members
419,567
Latest member
Naveen27mech

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top