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

[axkoam]

Hey guys,
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(file path)

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, "file path"

Open "file path" 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.

 

[axkoam]

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?