Details

Private Sub HeadlineEmailerBtn_Click()



Dim oApp As New Outlook.Application

Dim oEmail As Outlook.MailItem

Dim fileName As String, todayDate As String, fileName2 As String

Dim Signature As String

Dim subject As String

Dim thebody As String



'date to be added to the end of the PDF and HTML files

subject = Format(LastSunday(), "DD-MM-YYYY")



'Export report in same folder as db with date stamp

todayDate = Format(Date, "MMDDYYYY")



'convert report into PDF and save in same folder

fileName = Application.CurrentProject.Path & "\Weekly Headline Figures " & subject & ".pdf"

DoCmd.OutputTo acReport, "HKBNOR", acFormatPDF, fileName, False



'convert report into html file and save in same folder

fileName2 = Application.CurrentProject.Path & "\HK BNO" & subject & ".html"

DoCmd.OutputTo acReport, "HKBNOR", acFormatHTML, fileName2, False



'Email the results of the report generated

Set oEmail = oApp.CreateItem(olMailItem)

With oEmail

    oEmail.SentOnBehalfOfName = "[email protected]"

    .Recipients.Add "[email protected]"

    '.CC ="another address; next address"

    '.BCC = "whoever"

    .subject = "Headlines for week ending " & LastSunday()

    .Body = thebody

  

    'this runs the function(test)that inserts the HTML report into the body of the email

    .HTMLBody = test(fileName2)

  

    'this is the salutation and text I want inserting into the body of the email

    thebody = "Hi everyone, here are the Weekly Headlines Figures" & vbNewLine & vbNewLine & _

    "Data drawn from a mix of PRAU and Local MI (and thus subject to change)." & vbNewLine & _

    "Any issues with these or further queries please just let us know, and otherwise we'll provide updated figures during the One Pager later this week." & vbNewLine & vbNewLine & _

    "Regards"



    .Attachments.Add fileName

    .Attachments.Add fileName2

      

        .Display

    '.Send

End With



Set oApp = Nothing

Set oEmail = Nothing



MsgBox "Email successfully sent!", vbInformation, "EMAIL STATUS"


End Sub
Copy

Thank you Kevin.  

I have already tried this as I am quite conversant with HTML and web design, but I think I must be missing a probable easy fix.  Not sure if I have to declare something or my settings are incorrect.  Can't see the woods for the trees etc.

Try including your text in your html document, then assign everything to .HTMLBody.  Or you can include your text in your report, then convert the report to HTML.

Hi Kevin, Yes that is one way that I tried too.  It worked but the report is pretty full and the text (4 lines) spilled over (think it made the report too big).  Anyway, I will probably use that method as I can't find any other way.  Thank you so much for your help.  Kind regards... Sandi

Sandi  The page length of an email will vary depending on the device, the email app, font size, etc., used by the recipient.  So I wouldn't worry about it, because you wouldn't know how long the email would appear to the recipient.  If the report is too long, you may just attach it as an attachment.