IBM Lotus email through excel macro

[kamdardharmesh]

I am trying to send IBM Lotus email through excel macro and I am using following code. However, while sending mail it goes to only first precipitant.

one cell is having multiple email ids, which I have put as "To", however it picks up only first emailid and rest are not coming in proper form.

Pls help.

Code is as under.
_______________

Sub Email1()




    For i = 1 To 2
 
 
    Range("O1") = i
    Application.DisplayAlerts = False
    ' File
 
    Sheets("MacroData").Select
    Range("A7").Select
    ActiveSheet.Range("$A$7:$L$200000").AutoFilter Field:=1, Criteria1:="<>#N/A" _
        , Operator:=xlAnd
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A7:L200000").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Columns("A:L").Select
    Columns("A:L").EntireColumn.AutoFit
    Range("A1").Select
    ActiveWindow.DisplayGridlines = False
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Selection.End(xlUp).Select
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\dharmesh4033\Desktop\Lotus_April_Macro\Data.xls", FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    ActiveWindow.Close
    Sheets("MacroData").Select
'    ActiveSheet.Previous.Select
    Range("A7:L7").Select


'Part 2

Dim oSess As Object
Dim oDB As Object
Dim oDoc As Object
Dim oItem As Object
Dim direct As Object
Dim Var As Variant
Dim flag As Boolean

Set oSess = CreateObject("Notes.NotesSession")
Set oDB = oSess.GETDATABASE("", "")
Call oDB.OPENMAIL
flag = True
If Not (oDB.IsOpen) Then flag = oDB.Open("", "")

If Not flag Then
MsgBox "Can't open mail file: " & oDB.SERVER & " " & oDB.FILEPATH
GoTo exit_SendAttachment
End If
On Error GoTo err_handler

'Building Message
Set oDoc = oDB.CREATEDOCUMENT
Set oItem = oDoc.CREATERICHTEXTITEM("BODY")
oDoc.Form = "Memo"
'oDoc.Subject = "New Subject in here"
oDoc.Subject = Range("S3").Value
oDoc.sendto = Range("P1").Value
'oDoc.sendCc = Range("P2").Value



'oDoc.sendto = "dharmeshk@dcbbank.com"
oDoc.body = "Dear All," & vbNewLine & _
            "" & vbNewLine & _
            "New year has already started and as we all have decided during the concall, this year belongs to Sarfaroshi ki Tamanna, this year belongs to exponential growth, this year belongs to customer engagement, this year belongs to CASA!" & vbNewLine & _
            "" & vbNewLine & _
            "To help you on CASA Growth & Customer engagement, we need to start the year with bang. We need to engage with our customers, so they not only maintain their CASA balance but increase the same." & vbNewLine & _
            "" & vbNewLine & _
            "Enclosed file contains list of customers, who are very important for your branch. These are crème customers of your branch. Some of them are keeping good balance with us, while many of them have reduced banking with us or lowered the value. Their past behaviour shows that they can give good amount of CASA to you (provided you engage with them). During next few months, we need to engage with these customers with various ways & means. You will invite these customers in future events (Dinner meets, Musical events, etc), which you will organise." & vbNewLine & _
            "" & vbNewLine & _
            "These customers are sent to you in April itself, so you can plan events for your branch. As and when you finish the events, pls write back to us with following details for each customer (1) Date of the event for which customer was invited, (2) Whether he has attended or not (3) Type of event." & vbNewLine & _
            "" & vbNewLine & _
            "" & vbNewLine & _
            "Pls make best of use enclosed list of customers. These customers can make or break your Goalsheet. This is just a starting, we will come back with many engagement activities, which will help you in growing your business. We all will make sure that, your branch grows CASA by 100 Cr at the end of the year! So let's start the year with Sarfaroshi ki Tamanna!" & vbNewLine & _
            "" & vbNewLine & _
            "" & vbNewLine & _
            "With Warm Regards," & vbNewLine & _
            "Dharmesh Kamdar" & vbNewLine & _
            "Head - Portfolio & Analytics, Retail Deposits" & vbNewLine & _
            "" & vbNewLine & _
            "" & vbNewLine & _
            ""


oDoc.postdate = Date
oDoc.SaveMessageOnSend = True

'Attaching DATABASE
Call oItem.EmbedObject(1454, "", "C:\Users\dharmesh4033\Desktop\Lotus_April_Macro\Data.xls")
oDoc.visable = True
'Sending Message
oDoc.SEND False
exit_SendAttachment:
On Error Resume Next
Set oSess = Nothing
Set oDB = Nothing
Set oDoc = Nothing
Set oItem = Nothing
'Done
Exit Sub
err_handler:
If Err.Number = 7225 Then
MsgBox "File doesn't exist"
Else
MsgBox Err.Number & " " & Err.Description
End If
On Error GoTo exit_SendAttachment

'----- This is 2nd Part

'StopMacro:
'  With Application
  '      .ScreenUpdating = True
  '    .EnableEvents = True
    'End With
 
'    ActiveWorkbook.EnvelopeVisible = False
 

    Next i
    Sheets("MacroData").Select
'    ActiveSheet.Previous.Select
    Range("A7:L7").Select
 
    Application.DisplayAlerts = True


End Sub

--------------------------------------------------------------------------------------------
MOD EDIT: Added code tag.

 

[Chihiro]

I don't think that there are many of us that use Lotus in this forum. You may have better luck asking in Lotus forum.

Also, please use code tag (<> button) to post code. It makes it much easier for others to read code. I have edited your post to reflect this for now.

Having said that, what exactly is contained in P1? Is email strung together by "," or ";"? I'd recommend posting how exactly your workbook is set up (along with some dummy data).

 

[kamdardharmesh]

Thanks, will take care next time.

In cell P1, have kept emailIds separated by ",".

 

[Chihiro]

Then try using semicolon. It is often used to separate email addresses in mail clients.