Email from Excel via VBA through Lotus Notes with attachments

[Bengt]

Hi,
I am trying to send email from EXCEL/VBA through Lotus Notes and to attach files to the email that I send. After having checked a few fora, I know now how do the email thing, and I also know how to attach one file to the mail, but how do I do to attach any number of files?

The code I use looks something like this:
I have it in a function, so all the necessary info about recipient, subject and the name of the file that I want to attach etc is passed through the arguments of this function.

Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.sendto = Recipient
MailDoc.Subject = Subject
MailDoc.Body = Bodytext
MailDoc.SAVEMESSAGEONSEND = SaveIt

'Set up the embedded object and attachment and attach it
If Attachment <> "" Then
Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
'MailDoc.CREATERICHTEXTITEM ("Attachment")
End If

'Send the document
The section in red are the statements I use to attach the file, the name of which is in the string Attachment. But how do I do attach more files?

Grateful for any help

Bengt

 

[Andrew Poulsom]

This may help you:

http://www.mrexcel.com/forum/excel-...d-lotus-notes-email-multiple-attachments.html

 

[Bengt]

Thank you Andrew. I was able to use a variant of the code that I found on the link that you suggested.

Bengt

 

[hoba]

Hi Andrew

I need please your help to send the current saved file to a lotus notes (this is my default email program); I need to use vlookup formula for the recipient, I don't want to send the email just the step to press the send botton , this will allow me to verify every thing befor sending the email.



http://www.mrexcel.com/forum/excel-...d-lotus-notes-email-multiple-attachments.html[/QUOTE]

 

[hoba]

Hi Bengt
I need please your help to send the current saved file to a lotus notes (this is my default email program); I need to use vlookup formula for the recipient, I don't want to send the email just the step to press the send botton , this will allow me to verify every thing befor sending the email.

Hi,
I am trying to send email from EXCEL/VBA through Lotus Notes and to attach files to the email that I send. After having checked a few fora, I know now how do the email thing, and I also know how to attach one file to the mail, but how do I do to attach any number of files?

The code I use looks something like this:
I have it in a function, so all the necessary info about recipient, subject and the name of the file that I want to attach etc is passed through the arguments of this function.

Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.sendto = Recipient
MailDoc.Subject = Subject
MailDoc.Body = Bodytext
MailDoc.SAVEMESSAGEONSEND = SaveIt

'Set up the embedded object and attachment and attach it
If Attachment <> "" Then
Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
'MailDoc.CREATERICHTEXTITEM ("Attachment")
End If

'Send the document
The section in red are the statements I use to attach the file, the name of which is in the string Attachment. But how do I do attach more files?

Grateful for any help

Bengt

 

[taurean]

On Ron De Bruin's site you will come across many useful variations of LN OLE automation codes (author is XLDennis). Maybe you'll get something which is very close to your requirement and modify it:

Lotus Notes Mail code examples from Dennis Wallentin

 

[hoba]

I am stil confused I just need a simple macro to send to lotus note the currrent saved excel file, the recipient address and msg title are found in A6 and B6 ,the message body is fixed. Can any one help

 

[taurean]

If you are not going to check then it will be very difficult:

See the code provided in the link below:
Lotus Notes: Send a workbook as attachment to a created e-mail

 

[adriana_m]

Hi, I`m using excel 2013 with the Lotus Notes. I tried to send an e-mail and attach one file. The e-mail is sent, but without attachment. Here is the hole code i use:

 Sub nomifiles()  ' ' Scrive l'elenco dei nomi dei files contenuti nella directory '    Dim sFile                       As String    Dim fogli                       As String    Dim riga                        As Long     fogli = Range("B1").Value    If Right(fogli, 1) <> "\" Then fogli = fogli & "\"     riga = 7    ' Pulisce l'elenco    With Sheets("Data")       .Range("C7", .Range("C7").End(xlDown)).ClearContents       ' Cerca in tutti i files nella directoy source       sFile = Dir(fogli & "\*.xls")       If sFile <> "" Then          Do While sFile <> ""             .Range("C" & riga).Value = sFile             riga = riga + 1             sFile = Dir()          Loop       Else          MsgBox "Non ci sono files."       End If    End With End Sub  Sub LotusMail() ' ' Invio via Mail Lotus Notes dei files '  ' setting up various objects Dim Maildb As Object Dim UserName As String Dim MailDbName As String Dim MailDoc As Object Dim attachME As Object Dim Session As Object Dim EmbedObj1 As Object Dim recipient As String Dim ccRecipient As String Dim bccRecipient As String Dim subject As String Dim bodytext As String Dim Attachment As Object  For riga = 7 To 100   indirizzo = "A" & riga   oggetto = "B" & riga   allegato = "C" & riga      ' setting up all sending recipients   recipient = ThisWorkbook.Worksheets("Data").Range(indirizzo).Value   ccRecipient = ThisWorkbook.Worksheets("Data").Range("B3").Value   'bccRecipient = ""   subject = ThisWorkbook.Worksheets("Data").Range(oggetto).Value   bodytext = ThisWorkbook.Worksheets("Data").Range("B2").Value    '// Lets check to see if form is filled in Min req =Recipient, Subject, Body Text   If recipient = vbNullString Or recipient = "0" Or subject = vbNullString Or bodytext = vbNullString Then      MsgBox "Recipient, Subject and or Body Text is NOT SET! in row " & riga, vbCritical + vbInformation      Exit Sub   End If    ' creating a notes session   Set Session = CreateObject("Notes.NotesSession")   UserName = Session.UserName   MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"   Set Maildb = Session.GETDATABASE("", MailDbName)    If Maildb.IsOpen <> True Then      On Error Resume Next      Maildb.OPENMAIL   End If    Set MailDoc = Maildb.CreateDocument   MailDoc.Form = "Memo"    ' loading the lotus notes e-mail with the inputed data   With MailDoc      .SendTo = recipient      .copyto = ccRecipient      '.blindcopyto = bccRecipient      .subject = subject      .Body = bodytext   End With    ' saving message   MailDoc.SaveMessageOnSend = True    Attachment = ThisWorkbook.Worksheets("Data").Range(allegato).Value  If Attachment1 <> "" Then Set attachME = MailDoc.CREATERICHTEXTITEM("Attachment") Set EmbedObj = attachME.EMBEDOBJECT(1454, "", Attachment, "Attachment") 'MailDoc.CREATERICHTEXTITEM ("Attachment") End If    ' send e-mail !!!!   MailDoc.PostedDate = Now()   ' if error in attachment or name of recipients   On Error GoTo errorhandler1    MailDoc.Send 0, recipient    Set Maildb = Nothing   Set MailDoc = Nothing   Set attachME = Nothing   Set Session = Nothing   Set EmbedObj1 = Nothing  Next  'Unload Me Exit Sub ' setting up the error message errorhandler1: MsgBox "Incorrect name supplied or the attachment has not attached," & _ "or your Lotus Notes has not opened correctly. Recommend you open up Lotus Notes" & _ "to ensure the application runs correctly and that a vaild connection exists"  Set Maildb = Nothing Set MailDoc = Nothing Set attachME = Nothing Set Session = Nothing Set EmbedObj1 = Nothing ' unloading the userform 'Unload Me  End Sub

 

[Andrew Poulsom]

Dir returns only the filename, so that's all you have in column C. When attaching the file you need to include its path.