Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2012
    Posts
    38

    Unanswered: Trying to Merge an HTML Report as Email Body Script with an Access 2007 Generated Ema

    I can get this 1st script to auto-generate an email (outlook 2010), and I can get the 2nd script to open an unaddressed email with my report in the body, but I can't get the exporthtml script integrated into the MissingInfoRouteMessage script. Any tips?

    The script I have for the Access 2007 generated email is:

    Private Sub MissingInfoRouteMessage()
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim str_SQL As String
    Dim str_SQL1 As String
    Dim varX As Variant

    Set objOutlook = CreateObject("Outlook.Application")

    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    Export = exporthtml '**(this being my pathetic attempt at merging, see next script)
    varX = DLookup("[E-Mail]", "t_Routing", "[Mfg_Cd] is not null")

    With objOutlookMsg

    'Set objOutlookAttach = .Attachments.Add(\\Usdis01ap01nas1\sss\PBG Operations\Applications\Report-q_Workflow.html)

    Set objOutlookRecip = .Recipients.Add(varX)
    objOutlookRecip.Type = olTo

    Set objOutlookRecip = .Recipients.Add("")
    objOutlookRecip.Type = olCC
    .Subject = "International Authorization"
    .HTMLBody = "Hi Team,<br>Please let me know if the following orders are okay to approve." & vbCrLf & vbCrLf & Export
    .Importance = olImportanceHigh

    For Each objOutlookRecip In .Recipients
    objOutlookRecip.Resolve
    If Not objOutlookRecip.Resolve Then
    objOutlookMsg.Display
    End If
    Next
    .Send

    End With
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing
    End Sub


    And this HTML script that converts a report to text to put in the body of an email:

    Function exporthtml()

    Dim strline, strHTML

    Dim OL As Outlook.Application
    Dim MyItem As Outlook.MailItem

    Set OL = New Outlook.Application
    Set MyItem = Outlook.Application.CreateItem(olMailItem)

    DoCmd.OutputTo acOutputReport, "Report-q_Workflow", acFormatHTML, "\\Usdis01ap01nas1\sss\PBG Operations\Applications\Report-q_Workflow.html"

    Open "\\Usdis01ap01nas1\sss\PBG Operations\Applications\Report-q_Workflow.html" For Input As 1
    Do While Not EOF(1)
    Input #1, strline
    strHTML = strHTML & strline
    Loop
    Close 1
    ' If OL2002 set the BodyFormat
    If Left(OL.Version, 2) = "10" Then
    MyItem.BodyFormat = olFormatHTML
    End If
    MyItem.HTMLBody = strHTML
    MyItem.Display

    End Function


    The next issue I'm having is that I need the email to send only specific parts of the report to specific email addresses. I.E. 6 tuples might get sent to one address, 4 tuples to a 2nd address, and so on. But I'd rather get this initial problem solved first (I welcome tips with this though).

    Thanks! Let me know if I didn't properly explain the background.

  2. #2
    Join Date
    Jun 2012
    Posts
    38
    I actually figured it out (for anyone following) using this combined script:

    Function exporthtml()

    Dim strline, strHTML
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim varX As Variant

    varX = DLookup("[E-Mail]", "t_Routing", "[Mfg_Cd] is not null")

    Set objOutlook = Outlook.Application
    Set objOutlookMsg = Outlook.Application.CreateItem(olMailItem)

    DoCmd.OutputTo acOutputReport, "Report-q_Workflow", acFormatHTML, "filepath"

    Open "filepath" For Input As 1
    Do While Not EOF(1)
    Input #1, strline
    strHTML = strHTML & strline
    Loop
    Close 1
    If Left(objOutlook.Version, 2) = "10" Then
    objOutlookMsg.BodyFormat = olFormatHTML
    End If
    objOutlookMsg.HTMLBody = "Hi Team,<br>Please let me know if the following orders are okay to approve." & vbCrLf & vbCrLf & strHTML & "<br>Thank You,<br>*<br>*<br>*br>*<br>*<br>*" & vbCrLf & vbCrLf

    With objOutlookMsg

    Set objOutlookRecip = .Recipients.Add(varX)
    objOutlookRecip.Type = olTo

    Set objOutlookRecip = .Recipients.Add("")
    objOutlookRecip.Type = olCC
    objExport = exporthtml
    .Subject = "International Authorization"
    .Importance = olImportanceHigh

    For Each objOutlookRecip In .Recipients
    objOutlookRecip.Resolve
    If Not objOutlookRecip.Resolve Then
    objOutlookMsg.Display
    End If
    Next
    .Send
    End With

    End Function

    **etc. removed for privacy

    The only issue is that VBA is messing up the formatting of the html query output. Anyone know how to customize the formatting?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •