Email a PDF File from Excel using Lotus Notes

[marykay]

Hello all

I'm extremely new to this whole VBA world and have a macro to save specific sheets in a workbook to a specific location. Is there any way I can modify this so that once these are saved they can be sent via email in Lotus Notes or saved as drafts? They need to be sent to different recipients if that makes a difference.

Any help on this would be greatly appreciated. I have been looking for a solution to this but can't seem to get this to work.

Sub SaveAsPDF()
'
' SaveAsPDF Macro
' Saves Each Sheet as a PDF in a specific location with the Tab name.
'
'
'
    Sheets("Sales Report").Select
    Range("A1:R237").Select
    FilePathHome = "I:\PARTS\Reports PDF - Do not remove"
'
    
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        FilePathHome & "" & sFileName & Range("E7").Value & " - " & sFileName & Range("E9").Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
'    
'
    Sheets("Inventory").Select
    Range("A1:R237").Select
    FilePathHome = "I:\PARTS\Reports PDF - Do not remove"
'
    
    Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        FilePathHome & "" & sFileName & Range("E7").Value & " - " & sFileName & Range("E9").Value & ".pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
'
'

End Sub

Any help with this would be greatly appreciated. Again, very sorry, very very new to all this.

 

[CalcSux78]

Welcome to the forum, MaryKay!

So your code will save two reports to PDF with a known file location. We just need to make a few minor changes to your code:

Sub SaveAsPDF()
'
' SaveAsPDF Macro
' Saves Each Sheet as a PDF in a specific location with the Tab name.
'
'
'
Dim sFileName As String
Private Const FilePathHome As String = "I:\PARTS\Reports PDF - Do not remove\"


Sheets("Sales Report").Select
Range("A1:R237").Select


'Assign filename
sFileName = FilePathHome & Range("E7").Value & " - " & Range("E9").Value & ".pdf"


Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sFileName, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False


Call SendWithLotus("Sales@Sales.net", sFileName)


'Repeat for 2nd file


Sheets("Inventory").Select
Range("A1:R237").Select


Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=sFileName, Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False


Call SendWithLotus("Inventory@Inventory.net", sFileName)
'wrapup
MsgBox ("Save and send complete")
End Sub

...And add this below your code in the same module:

Sub SendWithLotus(ByVal sTo As String, ByVal sAttach As String)
   Dim noSession As Object, noDatabase As Object, noDocument As Object
   Dim obAttachment As Object, EmbedObject As Object
   Dim stSubject As Variant
   Dim vaMsg As Variant
 
   Const EMBED_ATTACHMENT As Long = 1454
 
   If sTo = False Then Exit Sub
 
   'This is where you change the message body
   vaMsg = "Please review the attached report." & vbCrLf & vbCrLf & _
           "Kind regards!" & vbCrLf & _
           "Mary Kay"


   'Add the subject to the outgoing e-mail which also can be retrieved from the users
   'in a similar way as above.
   stSubject = "Weekly report"
 
   '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
 
   'Create the e-mail and the attachment.
   Set noDocument = noDatabase.CreateDocument
   Set obAttachment = noDocument.CreateRichTextItem("sAttach")
   Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", sAttach)
 
   'Add values to the created e-mail main properties.
   With noDocument
      .Form = "Memo"
      .SendTo = sTo
      .Subject = stSubject
      .Body = vaMsg
      .SaveMessageOnSend = True
   End With
 
   'Send the e-mail.
   With noDocument
      .PostedDate = Now()
      .Send 0, sTo
   End With
 
   '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 "The e-mail has successfully been created and distributed.", vbInformation
 
End Sub

Note: I do not have Lotus Notes installed, so I cannot test before posting. (See Cunningham's Law below) I modified code from Ron de Bruin to fit your scenario.

See:
Lotus Notes: Send a workbook as attachment to a created e-mail
Lotus Notes Mail code examples from Dennis Wallentin



P.S. Please post back any questions you have. I would rather you understand how it works so you can apply to future projects.

 

[marykay]

Thank you very much for your reply!

I've tried this and it keeps failing at this point:


If sTo = False Then Exit Sub

Run-Time Error 13 - Type mismatch
Am I doing something wrong?

I really appreciate your time with this. I would like to fully understand how this works as I can implement it with several other tasks I have going at the moment.

Thanks again!

 

[CalcSux78]

The variable is a string. I had forgotten to remove that line, but if you change False to "" you can leave the line there.