Solved

Send email and attachment (Lotus notes)

Posted on 2015-02-24
55
225 Views
Last Modified: 2015-02-25
Hi,

I have attached a workbook that has two sales managers who require reports to be sent to them.

The eventual list is 700 managers but I have added two for simplicity.

Each sales manager has booked or managed pdf and .xls

There will always be a booked or managed and there may be a .xls

Once the month is selected i need the code to loop down through all the sales managers and find the attachments in the path, attach them to a new mail and send the attachments with a subject of

"Monthly Sales"

I know this is a big ask as it is excel talking to lotus notes, any help would be greatly appreciated!

Thanks
Burst-Tool.xlsm
0
Comment
Question by:Seamus2626
  • 27
  • 22
  • 6
55 Comments
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
I'm assuming this is what you are looking for...

http://www.rondebruin.nl/win/s1/notes/notes.htm

Personally i refer to his codes in sending email from excel as they are easy to understand and quick to use..

Please let me know if you need any further help on this...

Saurabh...
0
 

Author Comment

by:Seamus2626
Comment Utility
Hi Saurabh,

There is some excellent & useful code on those links, what I need however is slightly different.

I have a load of pdf and .xls files with sales names amended, eg Blogs, Joe - Booked(Nov-14).pdf

So I need the code to query the path name ="'R':\SPM\Boris Info\Boris_Project\GBM\"&TEXT(E5,"mmm-yy")&"\Output)"

then find any files for Joe Bloggs and send them to his email address

The reason we need this automated is there are 700 sales manager, so manually sending would be very time consuming!

Many thanks
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
So if i understand correctly you want to go to in a folder? and read all the files in that folder with the manager name and then attach these files?

How do you identify the files with manager name by first name or last name??

Saurabh...
0
 

Author Comment

by:Seamus2626
Comment Utility
Yep, that is correct, so the manager will be called blogs, Joe and in my attached file you can see I have a mini mapping table, so Bloggs, Joe is mapped to joe.bloggs@abc.com

Many thanks
Seamus
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Can you give me some sample data to look at..One manager will be good enough..

What i'm looking for is your manager name and the attachment names which can exist in folder so that i can build a co-relation

Also im assuming this code will sit on top of lotus email code as this will just add the multiple attachments...for rest of the thing i believe you can refer to rounde brin site....

Saurabh...
0
 

Author Comment

by:Seamus2626
Comment Utility
Hi Saurabh,

Its in my first comment!!!
0
 

Author Comment

by:Seamus2626
Comment Utility
Here it is again ;-)
Burst-Tool.xlsm
0
 

Author Comment

by:Seamus2626
Comment Utility
Here is a sample pdf
Capture.PNG
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
If Joe Bloggs is a Notes user, you can direct the Notes mail directly to "Joe Bloggs", no need to maintain a list of addresses.
0
 

Author Comment

by:Seamus2626
Comment Utility
Okay, thanks Sief.

I will still need code to loop through the drive to find Joe Bloggs and create the email to send him his attachments

Thanks
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Few questions..

Are you able to figure out the code without attachments as that gives me an idea how i need to write for you..If not then will write from scratch..
In your folder path it's ending with a bracket..can you verify the path is it correct since path dont end with a end bracket..
Also do you know how many attachments are their or their can be many attachments? what i'm looking for max number like 3,4,5 or 10

Saurabh
0
 

Author Comment

by:Seamus2626
Comment Utility
Yep, max attachments would be 3. Il be able to fill in the generic pieces, so if you were to put c:\local etc and abc@microsoft.com I can work out where to put in my pieces

Thanks
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Their you go basis of the last data you provided and sample use the following code...This is your complete code which should so what you are looking for..

Again i'm not able to test it since i dont have the lotus notes with me..

Public rng As Range, cell As Range
Const EMBED_ATTACHMENT1 As Long = 1454
Const EMBED_ATTACHMENT2 As Long = 1454
Const EMBED_ATTACHMENT3 As Long = 1454

Const stPath As String = "Path here"

Const stSubject As String = "Subject here"

Const vaMsg As Variant = "MSg here"



Sub get_data()

    Dim lrow As Long

    lrow = Cells(Cells.Rows.Count, "k").End(xlUp).Row

    Set rng = Range("K5:K" & lrow)

    For Each cell In rng
        If cell.Value <> "" Then send_email cell.Value, cell.Offset(0, 1).Value

    Next cell

End Sub

Sub send_email(str As String, str1 As String)

    Dim stFileName1 As String, stFileName2 As String, stFileName3 As String
    Dim vaRecipients As Variant

    Dim noSession As Object
    Dim noDatabase As Object
    Dim noDocument As Object
    Dim noEmbedObject1 As Object, noEmbedObject2 As Object, noEmbedObject3 As Object
    Dim noAttachment1 As Object, noAttachment2 As Object, noAttachment3 As Object
    Dim stAttachment1 As String, stAttachment2 As String, stAttachment3 As String

    'Create the list of recipients.
    vaRecipients = VBA.Array(str1)
    stFileName1 = str & " - Managed(Nov-14).pdf"
    stFileName2 = str & " - Booked(Nov-14).pdf"
    stFileName3 = str & " .xls"

    stAttachment1 = stPath & "\" & stFileName1
    stAttachment2 = stPath & "\" & stFileName2
    stAttachment3 = stPath & "\" & stFileName3

    'Instantiate the Lotus Notes COM's Objects.
    Set noSession = CreateObject("Notes.NotesSession")
    Set noDatabase = noSession.GETDATABASE("", "")

    'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL


    Set noDocument = noDatabase.CreateDocument
    Set noAttachment1 = noDocument.CreateRichTextItem("stAttachment1")
    Set noEmbedObject1 = noAttachment.EmbedObject(EMBED_ATTACHMENT1, "", stAttachment1)

    Set noAttachment2 = noDocument.CreateRichTextItem("stAttachment2")
    Set noEmbedObject2 = noAttachment.EmbedObject(EMBED_ATTACHMENT2, "", stAttachment2)


    Set noAttachment3 = noDocument.CreateRichTextItem("stAttachment3")
    Set noEmbedObject3 = noAttachment.EmbedObject(EMBED_ATTACHMENT3, "", stAttachment3)

    With noDocument
        .Form = "Memo"
        .SendTo = vaRecipients
        .CopyTo = vaCopyTo
        .Subject = stSubject
        .Body = vaMsg
        .SaveMessageOnSend = True
        .PostedDate = Now()
        .Send 0, vaRecipients
    End With


    Set noEmbedObject1 = Nothing
    Set noAttachment1 = Nothing
    Set noEmbedObject2 = Nothing
    Set noAttachment2 = Nothing
    Set noEmbedObject3 = Nothing
    Set noAttachment3 = Nothing
    Set noDocument = Nothing

    Set noDatabase = Nothing
    Set noSession = Nothing

End Sub

Open in new window


Saurabh...
0
 

Author Comment

by:Seamus2626
Comment Utility
Thanks Saurabh,

That looks good, on running then code I have encountered the error "object required" on the attached line

Thanks

On the plus side the below lines are all picking up the correct files!


    stAttachment1 = stPath & "\" & stFileName1
    stAttachment2 = stPath & "\" & stFileName2
    stAttachment3 = stPath & "\" & stFileName3
Capture.PNG
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Their you go i made certain changes in the code...try this and let me know..

Public rng As Range, cell As Range
Const EMBED_ATTACHMENT As Long = 1454

Const stPath As String = "Path here"

Const stSubject As String = "Subject here"

Const vaMsg As Variant = "MSg here"



Sub get_data()

    Dim lrow As Long

    lrow = Cells(Cells.Rows.Count, "k").End(xlUp).Row

    Set rng = Range("K5:K" & lrow)

    For Each cell In rng
        If cell.Value <> "" Then send_email cell.Value, cell.Offset(0, 1).Value

    Next cell

End Sub

Sub send_email(str As String, str1 As String)

    Dim stFileName1 As String, stFileName2 As String, stFileName3 As String
    Dim vaRecipients As Variant

    Dim noSession As Object
    Dim noDatabase As Object
    Dim noDocument As Object
    Dim noEmbedObject1 As Object, noEmbedObject2 As Object, noEmbedObject3 As Object
    Dim noAttachment1 As Object, noAttachment2 As Object, noAttachment3 As Object
    Dim stAttachment1 As String, stAttachment2 As String, stAttachment3 As String

    'Create the list of recipients.
    vaRecipients = VBA.Array(str1)
    stFileName1 = str & " - Managed(Nov-14).pdf"
    stFileName2 = str & " - Booked(Nov-14).pdf"
    stFileName3 = str & " .xls"

    stAttachment1 = stPath & "\" & stFileName1
    stAttachment2 = stPath & "\" & stFileName2
    stAttachment3 = stPath & "\" & stFileName3

    'Instantiate the Lotus Notes COM's Objects.
    Set noSession = CreateObject("Notes.NotesSession")
    Set noDatabase = noSession.GETDATABASE("", "")

    'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL


    Set noDocument = noDatabase.CreateDocument
    Set noAttachment1 = noDocument.CreateRichTextItem("stAttachment1")
    Set noEmbedObject1 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "stAttachment1", stAttachment1, "")

    Set noAttachment2 = noDocument.CreateRichTextItem("stAttachment2")
    Set noEmbedObject2 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "stAttachment1", stAttachment1, "")


    Set noAttachment3 = noDocument.CreateRichTextItem("stAttachment3")
    Set noEmbedObject3 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "stAttachment1", stAttachment1, "")

    With noDocument
        .Form = "Memo"
        .SendTo = vaRecipients
        .CopyTo = vaCopyTo
        .Subject = stSubject
        .Body = vaMsg
        .SaveMessageOnSend = True
        .PostedDate = Now()
        .Send 0, vaRecipients
    End With


    Set noEmbedObject1 = Nothing
    Set noAttachment1 = Nothing
    Set noEmbedObject2 = Nothing
    Set noAttachment2 = Nothing
    Set noEmbedObject3 = Nothing
    Set noAttachment3 = Nothing
    Set noDocument = Nothing

    Set noDatabase = Nothing
    Set noSession = Nothing

End Sub
                                          

Open in new window


Again if this still give you an error then in the original code which i wrote..check whether the attachment name is getting picked up properly or not by the following code:-

If Len(Dir(stAttachment1)) = 0 Then MsgBox "File does not exist" 
If Len(Dir(stAttachment2)) = 0 Then MsgBox "File does not exist" 
If Len(Dir(stAttachment3)) = 0 Then MsgBox "File does not exist" 

Open in new window


And if you get the above message then the file path is not correct and that need to be corrected in order to pick the file as attachment.

Saurabh...
0
 

Author Comment

by:Seamus2626
Comment Utility
I ran the attached code and got the same error but did not the "File does not exist" error

[/Sub send_email(str As String, str1 As String)

    Dim stFileName1 As String, stFileName2 As String, stFileName3 As String
    Dim vaRecipients As Variant

    Dim noSession As Object
    Dim noDatabase As Object
    Dim noDocument As Object
    Dim noEmbedObject1 As Object, noEmbedObject2 As Object, noEmbedObject3 As Object
    Dim noAttachment1 As Object, noAttachment2 As Object, noAttachment3 As Object
    Dim stAttachment1 As String, stAttachment2 As String, stAttachment3 As String
    
    If Len(Dir(stAttachment1)) = 0 Then MsgBox "File does not exist"
    If Len(Dir(stAttachment2)) = 0 Then MsgBox "File does not exist"
    If Len(Dir(stAttachment3)) = 0 Then MsgBox "File does not exist"


    'Create the list of recipients.
    vaRecipients = VBA.Array(str1)
    stFileName1 = str & " - Managed(Nov-14).pdf"
    stFileName2 = str & " - Booked(Nov-14).pdf"
    stFileName3 = str & " .xls"

    stAttachment1 = stPath & "\" & stFileName1
    stAttachment2 = stPath & "\" & stFileName2
    stAttachment3 = stPath & "\" & stFileName3

    'Instantiate the Lotus Notes COM's Objects.
    Set noSession = CreateObject("Notes.NotesSession")
    Set noDatabase = noSession.GETDATABASE("", "")

    'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL


    Set noDocument = noDatabase.CreateDocument
    Set noAttachment1 = noDocument.CreateRichTextItem("stAttachment1")
    Set noEmbedObject1 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "stAttachment1", stAttachment1, "")

    Set noAttachment2 = noDocument.CreateRichTextItem("stAttachment2")
    Set noEmbedObject2 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "stAttachment1", stAttachment1, "")


    Set noAttachment3 = noDocument.CreateRichTextItem("stAttachment3")
    Set noEmbedObject3 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "stAttachment1", stAttachment1, "")

    With noDocument
        .Form = "Memo"
        .SendTo = vaRecipients
        .CopyTo = vaCopyTo
        .Subject = stSubject
        .Body = vaMsg
        .SaveMessageOnSend = True
        .PostedDate = Now()
        .Send 0, vaRecipients
    End With


    Set noEmbedObject1 = Nothing
    Set noAttachment1 = Nothing
    Set noEmbedObject2 = Nothing
    Set noAttachment2 = Nothing
    Set noEmbedObject3 = Nothing
    Set noAttachment3 = Nothing
    Set noDocument = Nothing

    Set noDatabase = Nothing
    Set noSession = Nothing

End Sub

Open in new window

0
 

Author Comment

by:Seamus2626
Comment Utility
This is the file and the names/files exist
Burst-Tool.xlsm
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Are you sure? because you are testing for attachments before you actually give itshould have got the error? In your macro do you have on error resume next

if yes then can you remove that line?

Also change the line to this...

 If Dir(stAttachment1) = "" Then MsgBox "File does not exist"

Open in new window


Saurabh...
0
 

Author Comment

by:Seamus2626
Comment Utility
Yep, youre right, the msgbox error "File does not exist"

So my path is

Const stPath As String = "R:\SPM\Horis Info\Horis_Project\GBM\Nov-14\Output\CMB NA Data_With Macro"

My path is correct and the file LEE, Daric J - Managed (Nov-14).pdf exists

So it should find the file?

Many thanks Saurabh
Burst-Tool.xlsm
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Add the back slash at the end of this path... I mean this...

Const stPath As String = "R:\SPM\Horis Info\Horis_Project\GBM\Nov-14\Output\CMB NA Data_With Macro\"

Open in new window


Then see whether it gives you an error if not..then try the original code only which i shared and see if it works...

Like i said i can't test the code since i don't have lotus notes with me so i'm relying on you to do all the testing...

Saurabh...
0
 

Author Comment

by:Seamus2626
Comment Utility
I lined up the file names so there is no more

"File does not exist" message

I changed the path files to

R:\SPM\Horis Info\Horis_Project\GBM\Nov-14\Output\CMB NA Data_With Macro\

The full code I have is below with the attached file.

The code is debugging on the same line and same error message "Object required"

Regards,
Seamus

 Public rng As Range, cell As Range
Const EMBED_ATTACHMENT As Long = 1454

Const stPath As String = "R:\SPM\Horis Info\Horis_Project\GBM\Nov-14\Output\CMB NA Data_With Macro\"

Const stSubject As String = "Subject here"

Const vaMsg As Variant = "MSg here"



Sub get_data()

    Dim lrow As Long
    

    lrow = Cells(Cells.Rows.Count, "k").End(xlUp).Row

    Set rng = Range("K5:K" & lrow)

    For Each cell In rng
        If cell.Value <> "" Then send_email cell.Value, cell.Offset(0, 1).Value

    Next cell
    
    

End Sub

Sub send_email(str As String, str1 As String)

    Dim stFileName1 As String, stFileName2 As String, stFileName3 As String
    Dim vaRecipients As Variant

    Dim noSession As Object
    Dim noDatabase As Object
    Dim noDocument As Object
    Dim noEmbedObject1 As Object, noEmbedObject2 As Object, noEmbedObject3 As Object
    Dim noAttachment1 As Object, noAttachment2 As Object, noAttachment3 As Object
    Dim stAttachment1 As String, stAttachment2 As String, stAttachment3 As String



    'Create the list of recipients.
    vaRecipients = VBA.Array(str1)
    stFileName1 = str & " - Managed (Nov-14).pdf"
    stFileName2 = str & " - Booked (Nov-14).pdf"
    stFileName3 = str & ".xls"

    stAttachment1 = stPath & "\" & stFileName1
    stAttachment2 = stPath & "\" & stFileName2
    stAttachment3 = stPath & "\" & stFileName3

    'Instantiate the Lotus Notes COM's Objects.
    Set noSession = CreateObject("Notes.NotesSession")
    Set noDatabase = noSession.GETDATABASE("", "")

    'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL

    
    If Len(Dir(stAttachment1)) = 0 Then MsgBox "File does not exist"
    If Len(Dir(stAttachment2)) = 0 Then MsgBox "File does not exist"
    If Len(Dir(stAttachment3)) = 0 Then MsgBox "File does not exist"
    
    Set noDocument = noDatabase.CreateDocument
    Set noAttachment1 = noDocument.CreateRichTextItem("stAttachment1")
    Set noEmbedObject1 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "stAttachment1", stAttachment1, "")

    Set noAttachment2 = noDocument.CreateRichTextItem("stAttachment2")
    Set noEmbedObject2 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "stAttachment1", stAttachment1, "")


    Set noAttachment3 = noDocument.CreateRichTextItem("stAttachment3")
    Set noEmbedObject3 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "stAttachment1", stAttachment1, "")

    With noDocument
        .Form = "Memo"
        .SendTo = vaRecipients
        .CopyTo = vaCopyTo
        .Subject = stSubject
        .Body = vaMsg
        .SaveMessageOnSend = True
        .PostedDate = Now()
        .Send 0, vaRecipients
    End With


    Set noEmbedObject1 = Nothing
    Set noAttachment1 = Nothing
    Set noEmbedObject2 = Nothing
    Set noAttachment2 = Nothing
    Set noEmbedObject3 = Nothing
    Set noAttachment3 = Nothing
    Set noDocument = Nothing

    Set noDatabase = Nothing
    Set noSession = Nothing

End Sub


                                          

  

Open in new window

Burst-Tool.xlsm
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Try this code...

Public rng As Range, cell As Range
Const EMBED_ATTACHMENT As Long = 1454

Const stPath As String = "R:\SPM\Horis Info\Horis_Project\GBM\Nov-14\Output\CMB NA Data_With Macro\"

Const stSubject As String = "Subject here"

Const vaMsg As Variant = "MSg here"



Sub get_data()

    Dim lrow As Long

    lrow = Cells(Cells.Rows.Count, "k").End(xlUp).Row

    Set rng = Range("K5:K" & lrow)

    For Each cell In rng
        If cell.Value <> "" Then send_email cell.Value, cell.Offset(0, 1).Value

    Next cell

End Sub

Sub send_email(str As String, str1 As String)

    Dim stFileName1 As String, stFileName2 As String, stFileName3 As String
    Dim vaRecipients As Variant

    Dim noSession As Object
    Dim noDatabase As Object
    Dim noDocument As Object
    Dim noEmbedObject1 As Object, noEmbedObject2 As Object, noEmbedObject3 As Object
    Dim noAttachment1 As Object, noAttachment2 As Object, noAttachment3 As Object
    Dim stAttachment1 As String, stAttachment2 As String, stAttachment3 As String

    'Create the list of recipients.
    vaRecipients = VBA.Array(str1)
    stFileName1 = str & " - Managed(Nov-14).pdf"
    stFileName2 = str & " - Booked(Nov-14).pdf"
    stFileName3 = str & " .xls"

    stAttachment1 = stPath & "\" & stFileName1
    stAttachment2 = stPath & "\" & stFileName2
    stAttachment3 = stPath & "\" & stFileName3
    
    If Dir(stAttachment1) = "" Then MsgBox "File does not exist"
    If Dir(stAttachment2) = "" Then MsgBox "File does not exist"
    If Dir(stAttachment3) = "" = 0 Then MsgBox "File does not exist"

    'Instantiate the Lotus Notes COM's Objects.
    Set noSession = CreateObject("Notes.NotesSession")
    Set noDatabase = noSession.GETDATABASE("", "")



    'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL


    Set noDocument = noDatabase.CreateDocument
    Set noAttachment1 = noDocument.CreateRichTextItem("stAttachment1")
    Set noEmbedObject1 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment1)

    Set noAttachment2 = noDocument.CreateRichTextItem("stAttachment2")
    Set noEmbedObject2 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment2)


    Set noAttachment3 = noDocument.CreateRichTextItem("stAttachment3")
    Set noEmbedObject3 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment3)

    With noDocument
        .Form = "Memo"
        .SendTo = vaRecipients
        .CopyTo = vaCopyTo
        .Subject = stSubject
        .Body = vaMsg
        .SaveMessageOnSend = True
        .PostedDate = Now()
        .Send 0, vaRecipients
    End With


    Set noEmbedObject1 = Nothing
    Set noAttachment1 = Nothing
    Set noEmbedObject2 = Nothing
    Set noAttachment2 = Nothing
    Set noEmbedObject3 = Nothing
    Set noAttachment3 = Nothing
    Set noDocument = Nothing

    Set noDatabase = Nothing
    Set noSession = Nothing

End Sub
                                          

Open in new window


Saurabh...
0
 

Author Comment

by:Seamus2626
Comment Utility
That's fallen over on below line on the word stPath


 stAttachment1 = stPath & "\" & stFileName1

The error message is Compile error: Constant Expression Required

I attached a screenshot

Thanks Saurabh
Capture.PNG
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Try this...

Public rng As Range, cell As Range




Sub get_data()

    Dim lrow As Long
    

    lrow = Cells(Cells.Rows.Count, "k").End(xlUp).Row

    Set rng = Range("K5:K" & lrow)

    For Each cell In rng
        If cell.Value = "" Then send_email cell.Value, cell.Offset(0, 1).Value

    Next cell
    
    

End Sub

Sub send_email(str As String, str1 As String)

Const EMBED_ATTACHMENT As Long = 1454

Const stPath As String = "R:\SPM\Horis Info\Horis_Project\GBM\Nov-14\Output\CMB NA Data_With Macro\"

Const stSubject As String = "Subject here"

Const vaMsg As Variant = "MSg here"

    Dim stFileName1 As String, stFileName2 As String, stFileName3 As String
    Dim vaRecipients As Variant

    Dim noSession As Object
    Dim noDatabase As Object
    Dim noDocument As Object
    Dim noEmbedObject1 As Object, noEmbedObject2 As Object, noEmbedObject3 As Object
    Dim noAttachment1 As Object, noAttachment2 As Object, noAttachment3 As Object
    Dim stAttachment1 As String, stAttachment2 As String, stAttachment3 As String



    'Create the list of recipients.
    vaRecipients = VBA.Array(str1)
    stFileName1 = str & " - Managed (Nov-14).pdf"
    stFileName2 = str & " - Booked (Nov-14).pdf"
    stFileName3 = str & ".xls"

    stAttachment1 = stPath & "\" & stFileName1
    stAttachment2 = stPath & "\" & stFileName2
    stAttachment3 = stPath & "\" & stFileName3

    'Instantiate the Lotus Notes COM's Objects.
    Set noSession = CreateObject("Notes.NotesSession")
    Set noDatabase = noSession.GETDATABASE("", "")

    'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL

    
    If Len(Dir(stAttachment1)) = 0 Then MsgBox "File does not exist"
    If Len(Dir(stAttachment2)) = 0 Then MsgBox "File does not exist"
    If Len(Dir(stAttachment3)) = 0 Then MsgBox "File does not exist"
    
    Set noDocument = noDatabase.CreateDocument
    Set noAttachment1 = noDocument.CreateRichTextItem("stAttachment1")
    Set noEmbedObject1 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "stAttachment1", stAttachment1, "")

    Set noAttachment2 = noDocument.CreateRichTextItem("stAttachment2")
    Set noEmbedObject2 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "stAttachment1", stAttachment1, "")


    Set noAttachment3 = noDocument.CreateRichTextItem("stAttachment3")
    Set noEmbedObject3 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "stAttachment1", stAttachment1, "")

    With noDocument
        .Form = "Memo"
        .SendTo = vaRecipients
        .CopyTo = vaCopyTo
        .Subject = stSubject
        .Body = vaMsg
        .SaveMessageOnSend = True
        .PostedDate = Now()
        .Send 0, vaRecipients
    End With


    Set noEmbedObject1 = Nothing
    Set noAttachment1 = Nothing
    Set noEmbedObject2 = Nothing
    Set noAttachment2 = Nothing
    Set noEmbedObject3 = Nothing
    Set noAttachment3 = Nothing
    Set noDocument = Nothing

    Set noDatabase = Nothing
    Set noSession = Nothing

End Sub

Open in new window

0
 

Author Comment

by:Seamus2626
Comment Utility
This code cant get  into sub "send_email"

it is looping through, it thinks the cell value is blank but its not

It is also saying "Object variable or With block variable not set"

So it goes all the way through to "over"

 Sub get_data()

    Dim lrow As Long
    

    lrow = Cells(Cells.Rows.Count, "k").End(xlUp).Row

    Set rng = Range("K5:K" & lrow)

    For Each cell In rng
        If cell.Value = "" Then send_email cell.Value, cell.Offset(0, 1).Value

    Next cell
    
    MsgBox "Over"

End Sub   

Open in new window

0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Can you check in your dataset is the K column in the range or not..?? Because in my dataset i have used K Column..can you please check that part..
0
 

Author Comment

by:Seamus2626
Comment Utility
Yip, I am using Col K

Ive attached the screenshot

Thanks
Capture.PNG
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Then it should go to send email option..Can you run the code step by step by F8 and see what is happening because it should go to send email...
0
 

Author Comment

by:Seamus2626
Comment Utility
I amended to     For Each cell In rng
        If cell.Value <> "" Then send_email cell.Value, cell.Offset(0, 1).Value

and it stepped through

However I still get the object required error on the same line

    Set noEmbedObject1 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "stAttachment1", stAttachment1, "")

many thanks
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
This code...should take care of that error and it should work...

Public rng As Range, cell As Range

Sub get_data()

    Dim lrow As Long

    lrow = Cells(Cells.Rows.Count, "k").End(xlUp).Row

    Set rng = Range("K5:K" & lrow)

    For Each cell In rng
        If cell.Value <> "" Then send_email cell.Value, cell.Offset(0, 1).Value

    Next cell

End Sub

Sub send_email(str As String, str1 As String)
    Const EMBED_ATTACHMENT As Long = 1454


    Const stPath As String = "Path here"

    Const stSubject As String = "Subject here"

    Const vaMsg As Variant = "MSg here"

    Dim stFileName1 As String, stFileName2 As String, stFileName3 As String
    Dim vaRecipients As Variant

    Dim noSession As Object
    Dim noDatabase As Object
    Dim noDocument As Object
    Dim noEmbedObject1 As Object, noEmbedObject2 As Object, noEmbedObject3 As Object
    Dim noAttachment1 As Object, noAttachment2 As Object, noAttachment3 As Object
    Dim stAttachment1 As String, stAttachment2 As String, stAttachment3 As String

    'Create the list of recipients.
    vaRecipients = VBA.Array(str1)
    stFileName1 = str & " - Managed(Nov-14).pdf"
    stFileName2 = str & " - Booked(Nov-14).pdf"
    stFileName3 = str & " .xls"

    stAttachment1 = stPath & "\" & stFileName1
    stAttachment2 = stPath & "\" & stFileName2
    stAttachment3 = stPath & "\" & stFileName3

    If Dir(stAttachment1) = "" Then MsgBox "Check the path"

    If Dir(stAttachment2) = "" Then MsgBox "Check the path"
    If Dir(stAttachment3) = "" Then MsgBox "Check the path"

    'Instantiate the Lotus Notes COM's Objects.
    Set noSession = CreateObject("Notes.NotesSession")
    Set noDatabase = noSession.GETDATABASE("", "")

    'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL


    Set noDocument = noDatabase.CreateDocument
    Set noAttachment1 = noDocument.CreateRichTextItem("stAttachment1")
    Set noEmbedObject1 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment1)

    Set noAttachment2 = noDocument.CreateRichTextItem("stAttachment2")
    Set noEmbedObject2 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment2)


    Set noAttachment3 = noDocument.CreateRichTextItem("stAttachment3")
    Set noEmbedObject3 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment3)

    With noDocument
        .Form = "Memo"
        .SendTo = vaRecipients
        .CopyTo = vaCopyTo
        .Subject = stSubject
        .Body = vaMsg
        .SaveMessageOnSend = True
        .PostedDate = Now()
        .Send 0, vaRecipients
    End With


    Set noEmbedObject1 = Nothing
    Set noAttachment1 = Nothing
    Set noEmbedObject2 = Nothing
    Set noAttachment2 = Nothing
    Set noEmbedObject3 = Nothing
    Set noAttachment3 = Nothing
    Set noDocument = Nothing

    Set noDatabase = Nothing
    Set noSession = Nothing

End Sub

Open in new window


I'm basically making my code whatever i can find on net since i cant run this code as i don't have lotus notes..

Saurabh...
0
 

Author Comment

by:Seamus2626
Comment Utility
I understand Saurabh

Its debugging on line

    Set noEmbedObject1 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment1)

So it has the attachment but it has no object to embed, does it need to understand that the object is PDF?

Where the file names and attachments are defined (the paths) do we need to define the objects as PDF or excel?

Thanks
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Nah my bad..these lines will be..

Set noAttachment1 = noDocument.CreateRichTextItem("stAttachment1")
    Set noEmbedObject1 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment1)

    Set noAttachment2 = noDocument.CreateRichTextItem("stAttachment2")
    Set noEmbedObject2 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment2)


    Set noAttachment3 = noDocument.CreateRichTextItem("stAttachment3")
    Set noEmbedObject3 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment3)

Open in new window


This....

Set noAttachment1 = noDocument.CreateRichTextItem("stAttachment1")
    Set noEmbedObject1 = noAttachment1.EmbedObject(EMBED_ATTACHMENT, "", stAttachment1)

    Set noAttachment2 = noDocument.CreateRichTextItem("stAttachment2")
    Set noEmbedObject2 = noAttachment2.EmbedObject(EMBED_ATTACHMENT, "", stAttachment2)


    Set noAttachment3 = noDocument.CreateRichTextItem("stAttachment3")
    Set noEmbedObject3 = noAttachment3.EmbedObject(EMBED_ATTACHMENT, "", stAttachment3)

Open in new window


Saurabh...
0
 

Author Comment

by:Seamus2626
Comment Utility
Got the object variable or with block variable not set error!
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
On which line you get this error?..On second thoughts use this code...

Public rng As Range, cell As Range

Sub get_data()

    Dim lrow As Long

    lrow = Cells(Cells.Rows.Count, "k").End(xlUp).Row

    Set rng = Range("K5:K" & lrow)

    For Each cell In rng
        If cell.Value <> "" Then send_email cell.Value, cell.Offset(0, 1).Value

    Next cell

End Sub

Sub send_email(str As String, str1 As String)
    Const EMBED_ATTACHMENT As Long = 1454


    Const stPath As String = "Path here"

    Const stSubject As String = "Subject here"

    Const vaMsg As Variant = "MSg here"

    Dim stFileName1 As String, stFileName2 As String, stFileName3 As String
    Dim vaRecipients As Variant

    Dim noSession As Object
    Dim noDatabase As Object
    Dim noDocument As Object
    Dim noEmbedObject1 As Object, noEmbedObject2 As Object, noEmbedObject3 As Object
    Dim noAttachment As Object, noAttachment1 As Object, noAttachment2 As Object, noAttachment3 As Object
    Dim stAttachment1 As String, stAttachment2 As String, stAttachment3 As String

    'Create the list of recipients.
    vaRecipients = VBA.Array(str1)
    stFileName1 = str & " - Managed(Nov-14).pdf"
    stFileName2 = str & " - Booked(Nov-14).pdf"
    stFileName3 = str & " .xls"

    stAttachment1 = stPath & "\" & stFileName1
    stAttachment2 = stPath & "\" & stFileName2
    stAttachment3 = stPath & "\" & stFileName3

    If Dir(stAttachment1) = "" Then MsgBox "Check the path"

    If Dir(stAttachment2) = "" Then MsgBox "Check the path"
    If Dir(stAttachment3) = "" Then MsgBox "Check the path"

    'Instantiate the Lotus Notes COM's Objects.
    Set noSession = CreateObject("Notes.NotesSession")
    Set noDatabase = noSession.GETDATABASE("", "")

    'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL


    Set noDocument = noDatabase.CreateDocument
    Set noAttachment1 = noDocument.CreateRichTextItem("stAttachment1")
    Set noEmbedObject1 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment1)

    Set noAttachment2 = noDocument.CreateRichTextItem("stAttachment2")
    Set noEmbedObject2 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment2)


    Set noAttachment3 = noDocument.CreateRichTextItem("stAttachment3")
    Set noEmbedObject3 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment3)

    With noDocument
        .Form = "Memo"
        .SendTo = vaRecipients
        .CopyTo = vaCopyTo
        .Subject = stSubject
        .Body = vaMsg
        .SaveMessageOnSend = True
        .PostedDate = Now()
        .Send 0, vaRecipients
    End With


    Set noEmbedObject1 = Nothing
    Set noAttachment1 = Nothing
    Set noEmbedObject2 = Nothing
    Set noAttachment2 = Nothing
    Set noEmbedObject3 = Nothing
    Set noAttachment3 = Nothing
    Set noDocument = Nothing

    Set noDatabase = Nothing
    Set noSession = Nothing

End Sub

Open in new window


Saurabh...
0
 

Author Comment

by:Seamus2626
Comment Utility
Got the object variable or with block variable not set error again

run time 91

Same place. It doesn't seem to recognise the object

When I hover over
Set noEmbedObject1 = nothing

Thanks Saurabh
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Does this works...

In additional you are getting this error at the end..Their should be an email already gone through lotus notes as the entire code is executed...

Public rng As Range, cell As Range

Sub get_data()

    Dim lrow As Long

    lrow = Cells(Cells.Rows.Count, "k").End(xlUp).Row

    Set rng = Range("K5:K" & lrow)

    For Each cell In rng
        If cell.Value <> "" Then send_email cell.Value, cell.Offset(0, 1).Value

    Next cell

End Sub

Sub send_email(str As String, str1 As String)
    Const EMBED_ATTACHMENT As Long = 1454


    Const stPath As String = "Path here"

    Const stSubject As String = "Subject here"

    Const vaMsg As Variant = "MSg here"

    Dim stFileName1 As String, stFileName2 As String, stFileName3 As String
    Dim vaRecipients As Variant

    Dim noSession As Object
    Dim noDatabase As Object
    Dim noDocument As Object
    Dim noEmbedObject1 As Object, noEmbedObject2 As Object, noEmbedObject3 As Object
    Dim noAttachment As Object, noAttachment1 As Object, noAttachment2 As Object, noAttachment3 As Object
    Dim stAttachment1 As String, stAttachment2 As String, stAttachment3 As String

    'Create the list of recipients.
    vaRecipients = VBA.Array(str1)
    stFileName1 = str & " - Managed(Nov-14).pdf"
    stFileName2 = str & " - Booked(Nov-14).pdf"
    stFileName3 = str & " .xls"

    stAttachment1 = stPath & "\" & stFileName1
    stAttachment2 = stPath & "\" & stFileName2
    stAttachment3 = stPath & "\" & stFileName3

    If Dir(stAttachment1) = "" Then MsgBox "Check the path"

    If Dir(stAttachment2) = "" Then MsgBox "Check the path"
    If Dir(stAttachment3) = "" Then MsgBox "Check the path"

    'Instantiate the Lotus Notes COM's Objects.
    Set noSession = CreateObject("Notes.NotesSession")
    Set noDatabase = noSession.GETDATABASE("", "")

    'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL


    Set noDocument = noDatabase.CreateDocument
    Set noAttachment1 = noDocument.CreateRichTextItem("stAttachment1")
    Set noEmbedObject1 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment1)

    Set noAttachment2 = noDocument.CreateRichTextItem("stAttachment2")
    Set noEmbedObject2 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment2)


    Set noAttachment3 = noDocument.CreateRichTextItem("stAttachment3")
    Set noEmbedObject3 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment3)

    With noDocument
        .Form = "Memo"
        .SendTo = vaRecipients
        .CopyTo = vaCopyTo
        .Subject = stSubject
        .Body = vaMsg
        .SaveMessageOnSend = True
        .PostedDate = Now()
        .Send 0, vaRecipients
    End With

On Error Resume Next

    Set noEmbedObject1 = Nothing
    Set noAttachment1 = Nothing
    Set noEmbedObject2 = Nothing
    Set noAttachment2 = Nothing
    Set noEmbedObject3 = Nothing
    Set noAttachment3 = Nothing
    Set noDocument = Nothing

    Set noDatabase = Nothing
    Set noSession = Nothing

End Sub

Open in new window


Saurabh...
0
 

Author Comment

by:Seamus2626
Comment Utility
No email has been sent Saurabh, the code is still tripping over at

Set noEmbedObject1 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment1)


Should we be looking to create a session of lotus notes somewhere?

eg

Set nSess = CreateObject("Lotus.NotesSession")
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Hmm made the changes in the code...

Public rng As Range, cell As Range

Sub get_data()

    Dim lrow As Long

    lrow = Cells(Cells.Rows.Count, "k").End(xlUp).Row

    Set rng = Range("K5:K" & lrow)

    For Each cell In rng
        If cell.Value <> "" Then send_email cell.Value, cell.Offset(0, 1).Value

    Next cell

End Sub

Sub send_email(str As String, str1 As String)
    Const EMBED_ATTACHMENT As Long = 1454


    Const stPath As String = "Path here"

    Const stSubject As String = "Subject here"

    Const vaMsg As Variant = "MSg here"

    Dim stFileName1 As String, stFileName2 As String, stFileName3 As String
    Dim vaRecipients As Variant

    Dim noSession As Object
    Dim noDatabase As Object
    Dim noDocument As Object
    Dim noEmbedObject1 As Object, noEmbedObject2 As Object, noEmbedObject3 As Object
    Dim noAttachment As Object, noAttachment1 As Object, noAttachment2 As Object, noAttachment3 As Object
    Dim stAttachment1 As String, stAttachment2 As String, stAttachment3 As String

    'Create the list of recipients.
    vaRecipients = VBA.Array(str1)
    stFileName1 = str & " - Managed(Nov-14).pdf"
    stFileName2 = str & " - Booked(Nov-14).pdf"
    stFileName3 = str & " .xls"

    stAttachment1 = stPath & "\" & stFileName1
    stAttachment2 = stPath & "\" & stFileName2
    stAttachment3 = stPath & "\" & stFileName3

    If Dir(stAttachment1) = "" Then MsgBox "Check the path"

    If Dir(stAttachment2) = "" Then MsgBox "Check the path"
    If Dir(stAttachment3) = "" Then MsgBox "Check the path"

    'Instantiate the Lotus Notes COM's Objects.
    Set noSession = CreateObject("Notes.NotesSession")
    Set noDatabase = noSession.GETDATABASE("", "")



    'If Lotus Notes is not open then open the mail-part of it.
    If noDatabase.IsOpen = False Then noDatabase.OPENMAIL


    Set noDocument = noDatabase.CREATEDOCUMENT
    noDocument.Form = "Memo"
    noAttachment = noDocument.CreateRichTextItem("BODY")
    
    
    
    
    
        noAttachment.EmbedObject EMBED_ATTACHMENT, "", stAttachment1
        noAttachment.EmbedObject EMBED_ATTACHMENT, "", stAttachment2
        noAttachment.EmbedObject EMBED_ATTACHMENT, "", stAttachment3
        

    With noDocument
        .sendto = vaRecipients
        .CopyTo = vaCopyTo
        .Subject = stSubject
        .Body = vaMsg
        .SAVEMESSAGEONSEND = True
        .PostedDate = Now()
        .Send 0, vaRecipients
    End With

    On Error Resume Next

    Set noEmbedObject1 = Nothing
    Set noAttachment1 = Nothing
    Set noEmbedObject2 = Nothing
    Set noAttachment2 = Nothing
    Set noEmbedObject3 = Nothing
    Set noAttachment3 = Nothing
    Set noDocument = Nothing

    Set noDatabase = Nothing
    Set noSession = Nothing

End Sub

Open in new window

0
 

Author Comment

by:Seamus2626
Comment Utility
Then noDatabase.OPENMAIL

I don't think line is correct, no mail is opening when it passes that
0
 

Author Comment

by:Seamus2626
Comment Utility
Would that explain why there is no object to attach?
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
I believe that line is basically opening lotusnotes if it's closed...so it open lotus notes...
Then if you read the next set of lines..once the lotus notes open..then it creates a document to it which is a mail document and then works towards creating another attachment...
0
 

Author Comment

by:Seamus2626
Comment Utility
Ya, its still debugging on the same line

 Set noEmbedObject1 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment1)
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
In the revised code that i gave to you it doesn't even have that line..so are you updating your codes which i have been giving to you or you are still running the old code??
0
 

Author Comment

by:Seamus2626
Comment Utility
Sorry!

Its now debugging on


 noAttachment = noDocument.CreateRichTextItem("BODY")

Error message is

"Rich text item BODY already exists"

Thanks!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
> Object variable not set
That means that a variable doesn't contain an object. Assuming that the code bugs on the line with EmbedObject, that would mean that noAttachment isn't an object.

PS I know virtually nothing about details of VB, but...
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
Body is created twice indeed: once as NotesRichTextItem and once as a Text field, in the With block. Please rename the first to "Attachments" or so. The mail will be sent irrespective of the name of the field.
0
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 500 total points
Comment Utility
I re-wrote the entire code..can you tell me if this works..or you still get an error..

Make sure you give your attachment path for sure..

Public rng As Range, cell As Range

Sub get_data()

    Dim lrow As Long

    lrow = Cells(Cells.Rows.Count, "k").End(xlUp).Row

    Set rng = Range("K5:K" & lrow)

    For Each cell In rng
        If cell.Value <> "" Then send_email cell.Value, cell.Offset(0, 1).Value

    Next cell

End Sub

Sub send_email(str As String, str1 As String)
'


Dim MailDbName As String
Dim Recipient As Variant
Dim ccRecipient As String
Dim Attachment1 As String
Dim Attachment2 As String
Dim Attachment3 As String
Dim Attachment4 As String
Dim Attachment5 As String
Dim Maildb As Object
Dim MailDoc As Object
Dim AttachME As Object
Dim AttachME2 As Object
Dim AttachME3 As Object
Dim AttachME4 As Object
Dim AttachME5 As Object
Dim Session As Object
Dim EmbedObj1 As Object
Dim EmbedObj2 As Object
Dim EmbedObj3 As Object
Dim EmbedObj4 As Object
Dim EmbedObj5 As Object
Dim stSignature As String

With Application
.ScreenUpdating = False
.DisplayAlerts = False

' Open and locate current LOTUS NOTES User

Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
Set Maildb = Session.GetDatabase("", MailDbName)
If Maildb.IsOpen = True Then
Else
Maildb.OPENMAIL
End If

' Create New Mail and Address Title Handlers

Set MailDoc = Maildb.CREATEDOCUMENT

MailDoc.Form = "Memo"


' Select range of e-mail addresses
Recipient = Array(str1)
MailDoc.SendTo = Recipient
MailDoc.Subject = "PUT YOUR SUBJECT HERE"
MailDoc.Body = _
"Now is the time for all good men to come too the aid of their country"
' Select Workbook to Attach to E-Mail

Dim stfilename1 As String, stfilename2 As String, stfilename3 As String
Dim stpath As String

stpath = "Your Path here"

    stfilename1 = str & " - Managed(Nov-14).pdf"
    stfilename2 = str & " - Booked(Nov-14).pdf"
    stfilename3 = str & " .xls"

    

MailDoc.SaveMessageOnSend = True
Attachment1 = stpath & "\" & stfilename1

If Attachment1 <> "" Then
On Error Resume Next
Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
Set EmbedObj1 = AttachME.embedobject(1454, "attachment1", Attachment1, "") 'Required File Name
On Error Resume Next
End If

Attachment2 = stpath & "\" & stfilename2 '"C:\YourFile.xls" ' Required File Name

If Attachment2 <> 0 Then
On Error Resume Next
Set AttachME2 = MailDoc.CREATERICHTEXTITEM("attachment2")
Set EmbedObj2 = AttachME.embedobject(1454, "attachment2", Attachment2, "") 'Required File Name
On Error Resume Next
End If

Attachment3 = stpath & "\" & stfilename3 '"C:\YourFile.xls" ' Required File Name

If Attachment3 <> "" Then
On Error Resume Next
Set AttachME3 = MailDoc.CREATERICHTEXTITEM("attachment3")
Set EmbedObj3 = AttachME.embedobject(1454, "attachment3", Attachment3, "") 'Required File Name
On Error Resume Next
End If

MailDoc.PostedDate = Now()
On Error GoTo errorhandler1
MailDoc.SEND 0, Recipient

Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set AttachME2 = Nothing
Set AttachME3 = Nothing
Set AttachME4 = Nothing
Set AttachME5 = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing
Set EmbedObj2 = Nothing
Set EmbedObj3 = Nothing
Set EmbedObj4 = Nothing
Set EmbedObj5 = Nothing
.ScreenUpdating = True
.DisplayAlerts = True
End With

errorhandler1:

Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set AttachME2 = Nothing
Set AttachME3 = Nothing
Set AttachME4 = Nothing
Set AttachME5 = Nothing
Set Session = Nothing
Set EmbedObj1 = Nothing
Set EmbedObj2 = Nothing
Set EmbedObj3 = Nothing
Set EmbedObj4 = Nothing
Set EmbedObj5 = Nothing

End Sub

Open in new window


Saurabh...
0
 

Author Closing Comment

by:Seamus2626
Comment Utility
Amazing!!!

Thank you so much for your help and persistence! that has worked a treat

I cant say thank you enough times!!!!

If you were in London I would bring you out for beers!!!!!!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
More tips:

A/ better use a Lotus.NotesSession instead of a Notes.NotesSession.

B/ (from the Domino Designer Help database) you must explicitly initialize a COM session with one of the following NotesSession methods. These methods do not apply to LotusScript applications.
Call session.Initialize("passwordOptional")
Call session.InitializeUsingNotesUserName("name", "passwordOptional")
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Awesome..and yeah we had to go through this..as i can't test the code to see what was happening since don't have lotus notes so took sometime to resolve...

And if i see line by line i don't see much the code that i gave to you and one which i gave you again..apart then changing the way it's getting attached and it's different then what it's their at Rounde Brin site which is a sort of surprise to me...

But glad at the end that it worked.. :-)

Saurabh...
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
You're welcome :-)

PS The guy is called Ron de Bruin...
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility
Good point Sjef Bosman..My bad i have been busy in so code..that my head is going crazy..but will take care...Thanks for correcting..
0
 

Author Comment

by:Seamus2626
Comment Utility
Thank you too Sjef Bosman for your help!

Such a relief to get this out!
0
 

Author Comment

by:Seamus2626
Comment Utility
PS theres one or two tidying up questions I have, but I will repost as the main thing is the files are attaching and sending, but I will repost to reflect fairness of points earned!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
Comment Utility
Don't worry, there's no problem, as I did virtually nothing here. :-)
If your other questions aren't Notes-related I'm afraid there's little I can do there...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now