How is the best way to add this revision to a sendobject function?

[gojets1721]

So I am using DoCmd.SendObject on a command to open up an email with an attachment from my DB.

I want to add some new code where if the email is sent, then a field in the form updates. However, if the user exits out of the email without sending, then nothing is updated.

My code is below. Anything special that I need to do with IF statements? Or is it as simple as adding the new code under the SendObject code?

All I want to do is update the status field to 'in progress' if the email is sent (i.e. Me.Status = 'In progress')

Private Sub btnSendEmail_Click()
On Error GoTo btnSendEmail_Click_Err

    Dim ReportName As String
    Dim Subject As String
    Dim ToRecipient As String
    Dim CCRecipient As String
    Dim BCCRecipient As String
    Dim Message As String
    
    ReportName = "rptEmailReport"
    
    Subject = "Notification"
    ToRecipient = ""
    CCRecipient = ""
    BCCRecipient = ""
    Message = ""
    
    DoCmd.RunCommand acCmdRefresh
    DoCmd.SendObject acReport, ReportName, acFormatPDF, ToRecipient, CCRecipient, BCCRecipient, Subject, Message, True, ""
    
btnSendEmail_Click_Exit:
    Exit Sub

btnSendEmail_Click_Err:

    MsgBox "Error #" & Err.Number & " - " & Err.Description, , "Error"

    Resume btnSendEmail_Click_Exit
    
End Sub

 

[MajP]

You may be able to do this only if you automate Outlook, but not the way you have it.

How do i know if an email was sent and not closed while sending it through vba excel

I have a vba code which generates a outlook email, populates with required To, CC, Subject and Body when i change a particular column in excel. And when the email is sent my status column updates to '

stackoverflow.com

Without Automating Outlook there is no way to communicate back to Access. The method you have may not even open Outlook but another email client.

 

[Isaac]

Agreeing with Maj, don't use SendObject at all.
As with most of the DoCmd items people use, there is a better way ... Automate the user's Outlook program, assuming you're in an Office-centric environment (which you probably are if you're giving them Access databases)

 

[gojets1721]

Agreeing with Maj, don't use SendObject at all.
As with most of the DoCmd items people use, there is a better way ... Automate the user's Outlook program, assuming you're in an Office-centric environment (which you probably are if you're giving them Access databases)

I'm not sure what you mean by automating Outlook (not your fault; I'm an idiot).

Could you explain or provide a guide?

Thanks

 

[MajP]

Automation means you manipulate another Microsoft office application through vba code. In the link I posted they created an object that allow them to manipulate Outlook objects.

 Set xOutApp = CreateObject("Outlook.Application")
                Set xMailItem = xOutApp.CreateItem(0)

                With xMailItem
                    .To = Cells(i, "I").Value
                    .CC = ccStr
                    .display
                    Signature = .HTMLBody
                    .Subject = "Feedback for " & Cells(i, "B").Value & " viz. " & Cells(i, "C").Value
                    .HTMLBody = html & "This request was assited by " & Cells(i, "K").Value & "<br><br>" & Signature
                    '.dispaly

                    '.Send
                End With

I would look below at some of the "Similar Threads" for some code. Or you can Google "control Outlook from Access" and should get good examples.

 

[MajP]

Here is a video that gives some OK background

 

[Isaac]

Now I will comment on something that has become so [insert frustrated term]'ed up over the years I cannot remember precisely where things stand at Outlook's most current version, (I know it went back and forth somewhat) - but if you get annoying popups from Outlook every time you try to automate sending an email, you can, depending on the version:
1) go into the Outlook Options > Trust Center > Trust Center Settings and go to Allow automated activity or some such named thing
or
2) use a free (I think still?) program called Redemption which essentially, (if you have permission at your company to install it on the user's machine), allows a bypass of those popups, IF you are in an Outlook version where microsoft decided for our own good we should no longer have that power

 

[gojets1721]

Automation means you manipulate another Microsoft office application through vba code. In the link I posted they created an object that allow them to manipulate Outlook objects.

 Set xOutApp = CreateObject("Outlook.Application")
                Set xMailItem = xOutApp.CreateItem(0)

                With xMailItem
                    .To = Cells(i, "I").Value
                    .CC = ccStr
                    .display
                    Signature = .HTMLBody
                    .Subject = "Feedback for " & Cells(i, "B").Value & " viz. " & Cells(i, "C").Value
                    .HTMLBody = html & "This request was assited by " & Cells(i, "K").Value & "<br><br>" & Signature
                    '.dispaly

                    '.Send
                End With

I would look below at some of the "Similar Threads" for some code. Or you can Google "control Outlook from Access" and should get good examples.

Okay thanks. I am using the sendobject largely because I'm sending a report from Access with the email. Any suggestions on how to include that in the above code. I'm not finding any guides online that include how to attach a MS Access Report to the email

Thanks!

 

[Minty]

You would have to output the report to a pdf file, save it then add it as an attachment.
This thread has some code

Solved - eMailing an ACCESS report in PDF and opening the new OUTLOOK mail in HTML format

I wrote an invoicing system in Access many years ago. I produce an invoice that i then send to Outlook using the default email button in the ribbon of Usoft Access after specified the format as PDF format. It does this ok and opens the new Outlook mail document (IN PLAIN TEXT Format) with the...

www.access-programmers.co.uk

 

[gojets1721]

You would have to output the report to a pdf file, save it then add it as an attachment.
This thread has some code

Solved - eMailing an ACCESS report in PDF and opening the new OUTLOOK mail in HTML format

I wrote an invoicing system in Access many years ago. I produce an invoice that i then send to Outlook using the default email button in the ribbon of Usoft Access after specified the format as PDF format. It does this ok and opens the new Outlook mail document (IN PLAIN TEXT Format) with the...

www.access-programmers.co.uk

Got it. Thank you! So I rewrote the code as suggested. It is below. I'm still not sure how to write it so that my 'status' field ONLY updates if an email is sent (maybe it's not possible).

The code below displays the outlook message and I only want 'Me.Status' to update if the user sends the email. If they exit out of the email without sending, ideally the code stops there. Any suggestions?

Private Sub btnSendEmail_Click()
On Error GoTo btnSendEmail_Click_Err

    Dim Msg As String
    Dim O As Outlook.Application
    Dim M As Outlook.MailItem
    Dim objOutlookAttach As Outlook.Attachment
    
    Set O = New Outlook.Application
    Set M = O.CreateItem(olMailItem)
    
    With M
        .BodyFormat = olFormatHTML
        .HTMLBody = "Please see attached"
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = "See Attached Report"
           .Display
    End With
    
    Set M = Nothing
    Set O = Nothing

    Me.Status = "Completed"
    
btnSendEmail_Click_Exit:
    Exit Sub

btnSendEmail_Click_Err:

    If Err = 2501 Then

    Else

    MsgBox "Error #" & Err.Number & " - " & Err.Description, , "Error"
    
    End If
    
    Resume btnSendEmail_Click_Exit
    
End Sub

 

[Minty]

I think the cancel action which is handled by the 2501 error could be your friend.

From memory if they cancel the send it raises a 2501 error, so you could use that to simply escape the process before updating your sent status.

 

[gojets1721]

I think the cancel action which is handled by the 2501 error could be your friend.

From memory if they cancel the send it raises a 2501 error, so you could use that to simply escape the process before updating your sent status.

So I looked at that and it doesn't raises any error unfortunately :/

 

[Minty]

Did you comment out the existing On Error statement before trying it?

 

[gojets1721]

Did you comment out the existing On Error statement before trying it?

Yes

 

[gojets1721]

I'm gonna create a new post since so much of my code has changed since I originally posted. I found a viable solution but can't seem to get it working. Thanks for the help!