• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Send email and attachment (Lotus notes)

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
Seamus2626
Asked:
Seamus2626
  • 27
  • 22
  • 6
1 Solution
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
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
 
Saurabh Singh TeotiaCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Seamus2626Author Commented:
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
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
Hi Saurabh,

Its in my first comment!!!
0
 
Seamus2626Author Commented:
Here it is again ;-)
Burst-Tool.xlsm
0
 
Seamus2626Author Commented:
Here is a sample pdf
Capture.PNG
0
 
Sjef BosmanGroupware ConsultantCommented:
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
 
Seamus2626Author Commented:
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
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
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
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
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
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
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
 
Seamus2626Author Commented:
This is the file and the names/files exist
Burst-Tool.xlsm
0
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
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
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
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
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
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
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
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
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
Yip, I am using Col K

Ive attached the screenshot

Thanks
Capture.PNG
0
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
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
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
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
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
Got the object variable or with block variable not set error!
0
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
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
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
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
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
Then noDatabase.OPENMAIL

I don't think line is correct, no mail is opening when it passes that
0
 
Seamus2626Author Commented:
Would that explain why there is no object to attach?
0
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
Ya, its still debugging on the same line

 Set noEmbedObject1 = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment1)
0
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
Sorry!

Its now debugging on


 noAttachment = noDocument.CreateRichTextItem("BODY")

Error message is

"Rich text item BODY already exists"

Thanks!
0
 
Sjef BosmanGroupware ConsultantCommented:
> 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
 
Sjef BosmanGroupware ConsultantCommented:
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
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
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
 
Sjef BosmanGroupware ConsultantCommented:
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
 
Saurabh Singh TeotiaCommented:
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
 
Sjef BosmanGroupware ConsultantCommented:
You're welcome :-)

PS The guy is called Ron de Bruin...
0
 
Saurabh Singh TeotiaCommented:
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
 
Seamus2626Author Commented:
Thank you too Sjef Bosman for your help!

Such a relief to get this out!
0
 
Seamus2626Author Commented:
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
 
Sjef BosmanGroupware ConsultantCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 27
  • 22
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now