Lotus Note Email Code

[stutray]

Hi,

Thanks to NateO I have been using the following code to Send XL data to Lotus Notes.

It works like a dream but the code as it stands just lists the data line by line in a single column.

I would like to mail the details of cells A1 to B3 in a proper format as follows:

Date 07/11/2005
Name Somebloke
Value £25,000.00

Im am a beginner in VB and have tried modifying this part of the code but have had no joy :

MailDoc.Body = _
Replace("The following NEW SUPPLIER request had been actioned today@" _
& Join(Application.Transpose(Range([a1], [a300].End(3))), "@") _
& "@@...", "@", vbCrLf)

Any help would be very much appreciated.......Thanks.

Here is the original code :

Sub SendNotesMail()
Dim Maildb As Object, UserName As String, MailDbName As String
Dim MailDoc As Object, Session As Object
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, _
    (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GetDatabase("", MailDbName)
If Maildb.IsOpen = True Then
    Else: Maildb.OPENMAIL
End If
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
MailDoc.SendTo = "Stuartluckhurst@ebgbakeries.co.uk" 'Nickname or full address
'MailDoc.CopyTo = Whomever
'MailDoc.BlindCopyTo = Whomever
MailDoc.Subject = "New Supplier Request notification"
MailDoc.Body = _
    Replace("The following NEW SUPPLIER request had been actioned today:@@" _
        & Join(Application.Transpose(Range([a1], [a300].End(3))), "@") _
            & "@@...", "@", vbCrLf)
MailDoc.SaveMessageOnSend = True
MailDoc.PostedDate = Now
On Error GoTo Audi
Call MailDoc.Send(False)
Set Maildb = Nothing:    Set MailDoc = Nothing:    Set Session = Nothing
Exit Sub
Audi:
Set Maildb = Nothing:    Set MailDoc = Nothing:    Set Session = Nothing
End Sub

 

[NateO]

Hello,

Join() requires a 1-d array, so that won't work.

Look at Dennis' KB here:

http://www.excelkb.com/default.aspx?cNode=1X5M7A

 

[Norie]

What's the original format of the data?

 

[stutray]

Hello,

Join() requires a 1-d array, so that won't work.

Look at Dennis' KB here:

http://www.excelkb.com/default.aspx?cNode=1X5M7A

Thanks for the link - Only problem is it doesn't run.
I am using this code and the Error reads :

Compile error - Expected: line number or label statement or end of statement.

I pasted this code in into a new module as normal - Any idea as to what am I doing wrong ?

Option Explicit 

 

'Function for finding the first top level window in the windows list 

'that meet the criteria. 

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _ 

      (ByVal lpClassName As String, ByVal lpWindowName As String) As Long 

 

 

Sub Send_Formatted_Range_Data() 

   Dim oWorkSpace As Object, oUIDoc As Object 

   Dim rnBody As Range 

   Dim lnRetVal As Long 

 

   Const stTo As String = "Excel@Microsoft.com" 

   Const stCC As String = "Lotus Notes@IBM.com" 

   Const stBody As String = vbCrLf & "As per agreement." & vbCrLf _ 

         & "Kind regards" & vbCrLf & "Dennis" 

   Const stSubject As String = "Report xxx" 

   Const stMsg As String = "An e-mail has been succesfully created and saved." 

 

   'Check if Lotus Notes is open or not. 

   lnRetVal = FindWindow("NOTES", vbNullString) 

 

   If lnRetVal = 0 Then 

      MsgBox "Please make sure that Lotus Notes is open!", vbExclamation 

      Exit Sub 

   End If 

 

   Application.ScreenUpdating = False 

 

   'A named range in the activesheet is in use. 

   Set rnBody = ActiveSheet.Range("Report") 

   rnBody.Copy 

 

   'Instantiate the Lotus Notes COM's objects. 

   Set oWorkSpace = CreateObject("Notes.NotesUIWorkspace") 

 

   On Error Resume Next 

   Set oUIDoc = oWorkSpace.ComposeDocument("", "mail\xldennis.nsf", "Memo") 

   On Error GoTo 0 

 

   Set oUIDoc = oWorkSpace.CurrentDocument 

 

   'Using LotusScript to create the e-mail. 

   Call oUIDoc.FieldSetText("EnterSendTo", stTo) 

   Call oUIDoc.FieldSetText("EnterCopyTo", stCC) 

   Call oUIDoc.FieldSetText("Subject", stSubject) 

   Call oUIDoc.FieldSetText("Body", stBody) 

   Call oUIDoc.GoToField("Body") 

   Call oUIDoc.Paste 

   Call oUIDoc.Save(True, False, False) 

   'If the e-mail also should be sent then add the following line. 

   'Call oUIDoc.Send(True) 

 

   'Release objects from memory. 

   Set oWorkSpace = Nothing 

   Set oUIDoc = Nothing 

 

   With Application 

      .CutCopyMode = False 

      .ScreenUpdating = True 

   End With 

 

   MsgBox stMsg, vbInformation 

 

   'Activate Lotus Notes. 

   AppActivate ("Notes") 

 

End Sub

 

[NateO]

That copies and pastes as a pitcure, I think... Is that acceptable?

If so, see the following:

http://www.mrexcel.com/board2/viewtopic.php?p=371210#371210

You didn't mention where your error is, so I'm not sure...