Help! SendUsingAccount using outputtohtml in an email!

[JeffBarker]

Hi guys, I hope this wet UK afternoon finds everyone well!

I'm creating a routine in Access 2007 to build an HTML email by sucking information out of a form into a report.

This all works tip top (if I do say so myself!), but my boss has thrown a spanner in the works by announcing that the two end users of the database I'm creating have each got an alternative email address that (you guessed it) needs to be used when sending these emails with the HTML in them.

I'm not the most advanced of programmers, and I've done the Google bit and come up with this SendUsingAccount property, but I'm having trouble working out how to insert this into my code without breaking it.

The code I have to build the HTML email is as follows:

Private Sub cmdSendConfirmation_Click()
On Error GoTo Err_cmdSendConfirmation_Click
        
    Dim BkgStatus, BkgEmail, BkgRef, BkgType, Client As String
    Dim EmailTo, EmailCC, Company, ClientSubject, Subject As String
    
    BkgStatus = Me.txtBookingStatus
    BkgEmail = Me.txtEmailAddress
    BkgRef = Me.txtBookingID
    Client = Me.txtContact
    EmailTo = Forms![frmBookingConfirmation]![tblLookUpOperationsEmail subform]![txtLookUpOperationsEmail_To]
    EmailCC = Forms![frmBookingConfirmation]![tblLookUpOperationsEmail subform]![txtLookUpOperationsEmail_CC]
    Company = Me.txtCompany
    ClientSubject = ("Order Confirmation - Ref: " & [BkgRef] & ".")
    Subject = ("Show 2013 Order " & [BkgRef] & " - " & [Company] & ".")
    
    If BkgStatus = "new" Then Exit Sub
    
'    Client Email
    
    Dim MyItem, OL, strHTML, strline As String
    
    If BkgEmail > "" Then
        Set OL = New Outlook.Application
        Set MyItem = Outlook.Application.CreateItem(olMailItem)
        
        DoCmd.OutputTo acOutputReport, "rptCustomerBookingEmail", acFormatHTML, ("C:\Show Sales\Order Confirmation - " & BkgRef & ".html"), , ("C:\Show Sales\Order Confirmation - " & BkgRef & ".html")
        
        Open ("C:\Show Sales\Order Confirmation - " & BkgRef & ".html") For Input As 1
        Do While Not EOF(1)
            Input #1, strline
            strHTML = strHTML & strline
        Loop
        Close 1
        
        If Left(OL.Version, 2) = "10" Then
            MyItem.BodyFormat = olFormatHTML
        End If
        
        MyItem.HTMLBody = strHTML
        MyItem.To = BkgEmail
        MyItem.Subject = ClientSubject
        MyItem.Display
        Else
            MsgBox "There is no email address for this client.", vbInformation
        End If

'    Internal

    Dim MyItemI, OLI, strHTMLI, strlineI As String
    
    Set OLI = New Outlook.Application
    Set MyItemI = Outlook.Application.CreateItem(olMailItem)
    
    DoCmd.OutputTo acOutputReport, "rptInternalBookingEmail", acFormatHTML, ("C:\Show Sales\Internal Email - " & BkgRef & ".html"), , ("C:\Show Sales\Order Confirmation - " & BkgRef & ".html")
    
    Open ("C:\Show Sales\Internal Email - " & BkgRef & ".html") For Input As 2
    Do While Not EOF(2)
    Input #2, strlineI
    strHTMLI = strHTMLI & strlineI
    Loop
    Close 1

    MyItemI.HTMLBody = strHTMLI
    MyItemI.To = EmailTo
    MyItemI.CC = EmailCC
    MyItemI.Subject = Subject
    MyItemI.Send

    Me.AcknSent = Now()
    DoCmd.Close acForm, "frmBookingConfirmation"

Exit_cmdSendConfirmation_Click:
    Exit Sub
    
Err_cmdSendConfirmation_Click:
    MsgBox Err.Description
    Resume Exit_cmdSendConfirmation_Click
End Sub

As I said, this all works fine - the routine sends an email to the client and then a confirmation to the guys in the office.

We're using Outlook 2007 on Windows 7 in an Exchange stylee.

What I'm after is a kind soul to amend my code to allow me to program in the ability for the two end users to send from a specific email account - ie, not their usual one.

Many thanks in advance, guys!!

 

[JeffBarker]

Did we have any suggestions on this one at all, guys and girls?! Still struggling with coming up with a solution!

 

[DrallocD]

Are you able to send using SMTP rather than the outlook client automation? If so you could google "vbScript to send email using CDO".

 

[JeffBarker]

Hi Dralloc,

First of all, thanks for replying!

After a bit of investigation, it turns out that the users' alternative email addresses are stored on a POP3/SMTP server, so this should work perfectly!

I'm going to have a play with things and then let you know how I get on!!

Thanks again!