CODEWITHSUNDEEP
vbaemailpdflotus-notes
user7415328
user7415328

Reputation: 1083

Print/Save IBM Notes Email as PDF before sending?

I am using the code below to create and send an email from excel using IBM Notes.

I have tried and tried to get this email to save into a folder as a PDF or just print it so i can print it as a PDF.

Whatever i try i cannot seem to get this to print/save as PDF. The rest of the code is working fine.

I came close, by using this piece of code (which saves the attachment from each email as it's being created).

Attachment = Range("F" & i).value
Set AttachME = doc.CREATERICHTEXTITEM("attachment")
Set EmbedObj = AttachME.EmbedObject(1454, "", Attachment, "")
EmbedObj .ExtractFile "C:\attach\" & EmbedObj .Name

I even tried changing this to:

Set doc = db.CreateDocument
doc.ExtractFile "C:\attach\" & "SomeFileName.pdf"

But alas this produces a object doesn't support this property or method error. I am also trying this:

doc.Print True, False

But still no luck.

My full code:

Sub Send()
ActiveSheet.DisplayPageBreaks = False
Dim answer As Integer
    answer = MsgBox("Are you sure you want to Send All Announcements?", vbYesNo + vbQuestion, "Notice")
    If answer = vbNo Then
    Exit Sub

    Else

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim Attachment As String
Dim WB3 As Workbook
Dim WB4 As Workbook
Dim Rng As Range
Dim db As Object
Dim doc As Object
Dim body As Object
Dim header As Object
Dim stream As Object
Dim session As Object
Dim i As Long
Dim j As Long
Dim j2 As Long
Dim server, mailfile, user, usersig As String
Dim LastRow As Long, LastRow2 As Long, WS As Worksheet
LastRow = Worksheets(1).Range("F" & Rows.Count).End(xlUp).Row  'Finds the last used row

j = 18

With ThisWorkbook.Worksheets(1)

For i = 18 To LastRow


'Start a session of Lotus Notes
Set session = CreateObject("Notes.NotesSession")
'This line prompts for password of current ID noted in Notes.INI
Set db = session.CurrentDatabase
Set stream = session.CreateStream
' Turn off auto conversion to rtf
session.ConvertMime = False



'Email Code

'Create email to be sent

Set doc = db.CreateDocument
doc.Form = "Memo"
Set body = doc.CreateMIMEEntity
Set header = body.CreateHeader("Promotion Announcement for week " & Range("I8").value & ", " & Range("T8").value & " - Confirmation required")
Call header.SetHeaderVal("HTML message")

'Set From
Call doc.ReplaceItemValue("Principal", "Food Specials <mailto:[email protected]>")
Call doc.ReplaceItemValue("ReplyTo", "[email protected]")
Call doc.ReplaceItemValue("DisplaySent", "[email protected]")

Call doc.ReplaceItemValue("Subject", "Promotion Announcement for week " & Range("I8").value & ", " & Range("T8").value & " - Confirmation required")

'To
Set header = body.CreateHeader("To")
Call header.SetHeaderVal(Range("N" & i).value)


'Email Body
Call stream.WriteText("<HTML>")
Call stream.WriteText("<font size=""3"" color=""black"" face=""Arial"">")
Call stream.WriteText("<p>Good " & Range("A1").value & ",</p>")
Call stream.WriteText("<p>Please see attached an announcement of the spot buy promotion for week " & Range("I8").value & ", " & Range("T8").value & ".<br>Please check, sign and send this back to us within 24 hours in confirmation of this order. Please also inform us of when we can expect the samples.</p>")
Call stream.WriteText("<p>The details are as follows:</p>")

'Insert Range
Set WB3 = Workbooks.Open(Range("F" & i).value)
With WB3.Sheets(1)
.Range("A20:J39").SpecialCells(xlCellTypeVisible).Select
Set Rng = Selection
End With

Call stream.WriteText(RangetoHTML(Rng))
WB3.Close SaveChanges:=False


'Attach file
Attachment = Range("F" & i).value
Set AttachME = doc.CREATERICHTEXTITEM("attachment")
Set EmbedObj = AttachME.EmbedObject(1454, "", Attachment, "")


Call stream.WriteText("<BR><p>Please note the shelf life on delivery should be 75% of the shelf life on production.</p></br>")
'Signature
Call stream.WriteText("<BR><p>Kind regards / Mit freundlichen Gr&#252;&#223;en,</p></br>")
Call stream.WriteText("<p><b>Lidl UK Food Specials Team</b></p>")

Call stream.WriteText("<table border=""0"">")
Call stream.WriteText("<tr>")
Call stream.WriteText("<td><img src=""http://www.lidl.co.uk/statics/lidl-uk/ds_img/layout/top_logo2016.jpg"" alt=""Mountain View""></td>")
Call stream.WriteText("<td><img src=""http://www.lidl.co.uk/statics/lidl-uk/ds_img/assets_x_x/BOQLOP_NEW%281%29.jpg"" alt=""Mountain View""></td>")
Call stream.WriteText("</tr>")
Call stream.WriteText("</table>")


Call stream.WriteText("</font>")
Call stream.WriteText("</body>")
Call stream.WriteText("</html>")

Call body.SetContentFromText(stream, "text/HTML;charset=UTF-8", ENC_IDENTITY_7BIT)

doc.Print True, False

doc.Save True, False
Call doc.PutInFolder("TEST")

Call doc.Send(False)

session.ConvertMime = True ' Restore conversion - very important


'Clean Up the Object variables - Recover memory
    Set db = Nothing
    Set session = Nothing
    Set stream = Nothing
    Set doc = Nothing
    Set body = Nothing
    Set header = Nothing

    'WB3.Close savechanges:=False

    Application.CutCopyMode = False

'Email Code

j = j + 1

Next i
End With




Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Success!" & vbNewLine & "Announcements have been sent."
MsgBox doc.GetItemValue("subject")(0)

End If
End Sub




Function RangetoHTML(Rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2010
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook

    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

    'Copy the range and create a new workbook to past the data in
    Rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With

    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         fileName:=TempFile, _
         Sheet:=TempWB.Sheets(1).name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With

    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")

    'Close TempWB
    TempWB.Close SaveChanges:=False

    'Delete the htm file we used in this function
    Kill TempFile

    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

Please can someone show me where I am going wrong?

Upvotes: 0

Views: 596

Answers (1)

Richard Schwartz
Richard Schwartz

Reputation: 14628

The Notes APIs don't have the ability to save a message as a PDF.

You can't pass a range to EmbedObject. EmbedObject wants a filename - for a file that you've already saved to disk. You can create a PDF and attach it to an email using EmbedObject. If someone has already created a PDF and attached it to an email, you can save the PDF to disk using ExtractFile - which, as you found through your second attempt is a method of the NotesRichTextItem class, not the NotesDocument class. And as for your final attempt, the NotesDocument class does not have a print method, either.

To the best of my knowledge, the only solutions for saving Notes email messages as PDF files require third-party commercial software. (There are some PDF-related open source projects on the OpenNTF website, but I believe they are all based on Lotus XPages technology, which you cannot access from VBA.)

Upvotes: 1

Related Questions