Send Word Doc as an attachment on Lotus Notes Email

Question

I have a Word Document that contains an email address and I need to send this document to different people using Lotus Notes, which is my default email client and to which I am already connected to. I would also like, if possible, to send the document as a PDF.

Please can someone help me write a macro that attaches the document as a PDF and sends it to all email addresses already in the Word document using Lotus Notes.

I have tried to use a piece of code that I found in this forum, but is not getting the email addresses:

Sub Send_mail_recipients()
Dim Text As String
Dim char As String
Dim rowcount, n_address, n_cells, Cell_Crt, CharNo As Integer
Dim Recipient(100) As Variant
 'With Application.ActiveWindow.Document
 'Activate the Document
  'n_address = 0
 Text = ""
  ActiveDocument.Tables(2).Select
     n_cells = Selection.Cells.Count
  For Cell_Crt = 1 To n_cells
 If Selection.Cells(Cell_Crt).Range.Text Like "*@*" Then
'Recipient(n_address) = Selection.Cells(Cell_Crt).Range.Text
Text = Text + Selection.Cells(Cell_Crt).Range.Text + ", "
'n_address = n_address + 1
End If
'Text = Selection.Cells(Cell_Crt).Range.Text
Next
End If

In the end I have decided to attach an excel file. The excel file is attached to an email address but I would like to attach this excel file as a PDF. I am not sure how to do it. This is the code am using to attach active excel file to an email

Sub LotusNotesExcelEmail()

Dim noSession As Object, noDatabase As Object, noDocument As Object
Dim obAttachment As Object, EmbedObject As Object
Dim stSubject As Variant, stAttachment As String
Dim vaRecipient As Variant, vaMsg As Variant

Const EMBED_ATTACHMENT As Long = 1454

'Retrieve the path and filename of the active workbook.
 stAttachment = ActiveWorkbook.FullName

'Initiate 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

'Create the e-mail and the attachment.
Set noDocument = noDatabase.CreateDocument
Set obAttachment = noDocument.CreateRichTextItem("stAttachment")
Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "",  stAttachment)


    'Get the name of the recipient from the user.
    vaRecipient = Worksheets("Invitación_curso").Range("B8").Value

 'Add values to the created e-mail main properties.
 With noDocument
.Form = "Memo"
.SendTo = vaRecipient
.Subject = "Solicitud Invitación Curso "
.Body = "Estimado xxxxx. Adjuntamos solicitud de invitación a curso……………!"
 .SaveMessageOnSend = True
End With


'Send the e-mail.

 With noDocument
.PostedDate = Now()
.SEND 0, vaRecipient
 End With
 Dim myMessage As String
 myMessage = MsgBox("Está seguro de que quiere enviar este correo?",    vbYesNo, "Está seguro?")

 If myMessage = vbYes Then
     With noDocument
         .PostedDate = Now()
         .SEND 0, vaRecipient
     End With
End If


'Release objects from the memory.
Set EmbedObject = Nothing
Set obAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing

'Activate Excel for the user.
'AppActivate "Microsoft Excel"
'MsgBox "El mensaje de correo se ha enviado correctamente", vbOKOnly
End Sub

[This](http://www.fabalou.com/vbandvba/lotusnotesmail.asp) might be a good point to start, please note that Stack Overflow is not a code-writing service but it's rather to help fixing an already attempted code. — Matteo NNZ, Jan 13 '15 at 17:29

score 0 · Answer 1 · answered Jan 14 '15 at 23:01

Like with all programming, break down the problem into smaller problems/steps. Then solve one of them at a time. Let's look at your problem and how to handle each one. I will assume that you want to run a VBA macro from within Word, when you have the specific document open.

1) Read the email address(es) from within the Word document. Since you know what the document look like, you have to figure this out on your own. Store the address(es) in an array (of string).

2) Create a PDF of the currently open document. I assume that Word have the same functionality today as OpenOffice, and that you can simply export the document as a PDF file. Should not be difficult to write. Just remember to store the name and path of the PDF you create (you should force the filename when you save it, preferably also put the file in your temp folder) so you can get the file later.

3) Use the Notes backend classes in COM to generate a new email, populate the address field, subject and body, and attach the PDF you created in step 2. You can take a look at a Lotusscript class I wrote to handle mail notifications, perhaps that can help you:

http://blog.texasswede.com/lotusscript-mail-notification-class/

What you need to remember is that the address fields are multi-value fields. You simply put the addresses in an array and store the array in that field. Also, if you want to send the email from another user than the one currently logged in, you must use the undocumented method of storing the email document in mail.box, instead of using teh Send method of the NotesDocument class.

Send Word Doc as an attachment on Lotus Notes Email

1 Answers1