Inserting Report in Outlook (1 Viewer)

M

MichaelWaimauku

Registered User.
Local time
Today, 11:29
Joined
Dec 6, 2012
Messages
57
Hi, I'm inserting a report based on the current open record into Outlook email. It all works fine except for a couple of things. If the email is cancelled, an error displays along with the report being left open - due to debugging. (Run-time error '2501': The SendObject action was cancelled). What code can be written to allow the completion if user cancels email (giving message saying, 'Action Cancelled by User' and then closing open report - just as if it was completed sucessfully)?

Private Sub Command25_Click()
Me.Dirty = False
DoCmd.OpenReport "nonconformity", acViewPreview, , "[nonconformid] =" & Me.NonConformID
DoCmd.SendObject acSendReport, , acFormatPDF, Forms![non conformity]![Contact] DoCmd.Close acReport, "NonConformity", acSaveNo
End Sub

Thanks in advance
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 00:29
Joined
Nov 30, 2011
Messages
8,494
Hello, what you need is to place a simple error handler.. something like..
Code: 
Private Sub Command25_Click()
[COLOR=Red]On Error GoTo errHandler[/COLOR]
    Me.Dirty = False
    DoCmd.OpenReport "nonconformity", acViewPreview, , "[nonconformid] =" & Me.NonConformID
    DoCmd.SendObject acSendReport, , acFormatPDF, Forms![non conformity]![Contact] DoCmd.Close acReport, "NonConformity", acSaveNo
[COLOR=Red]exitOnError:[/COLOR]
   [COLOR=Green] 'DoCmd.Close acReport, "nonconformity"[/COLOR]
    Exit Sub
[COLOR=Red]errHandler:
    [COLOR=Blue]If Err.Number <> 2501[B] Then[/B]
        Call MsgBox("An Error occured !!" & vbCrLf & vbCrLf & Err.Number & " : " & Err.Description, vbCritical)[/COLOR]
    Else
        Call MsgBox("Operation canceled by User !!", vbInformation)
    End If
    Resume exitOnError[/COLOR]
End Sub
I have highlighted the Error handling bit.. The reason I have a If/Else structure is because sometimes there might be other errors which might occur other than the 2501..

I have also commented out the part where you will do a manual close, this will ensure the report is closed.. even under normal circumstances.. You can change it if you want..

On a side note, learn how to Handle Errors, no matter in any procedure that will save you a lot of undesirable errors.
 
Last edited:
M

MichaelWaimauku

Registered User.
Local time
Today, 11:29
Joined
Dec 6, 2012
Messages
57
Hi Paul, I added as shown however when I run there's a syntax error on this line:
If Err.Number <> 2501

Also Compile Error, Expected: Then or GoTo

What needs to be added where? Thanks
 
Last edited:
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 00:29
Joined
Nov 30, 2011
Messages
8,494
I just edited the code.. a Then Keyword is required.. Sorry I missed that..
 
M

MichaelWaimauku

Registered User.
Local time
Today, 11:29
Joined
Dec 6, 2012
Messages
57
Don't worry, I sorted it, just added then after If Err.Number <> 2501 then
Thank you for your help Paul, used that link you sent to find the info. Good stuff
 
M

MichaelWaimauku

Registered User.
Local time
Today, 11:29
Joined
Dec 6, 2012
Messages
57
Since you've been so helpful, how can I end a message that say's it's completed successfully at the end of the script? E.g. MsgBox "Operation completed successfully"
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 00:29
Joined
Nov 30, 2011
Messages
8,494
Just before the Exit Sub statement use a MsgBox..
Code: 
[B]:[/B]
    Call MsgBox("Operation Completed Successfully", vbInformation)
exitOnError:
    Exit Sub
[B]:[/B]
 
M

MichaelWaimauku

Registered User.
Local time
Today, 11:29
Joined
Dec 6, 2012
Messages
57
Awesome Paul, works like a charm.

With the DoCmd.SendObject, do you know if the email that is generated can be set to HTML? Also, I notice that the signature does not show up with this generated email. Is it possible to have that displayed or is that a lack of functionality with the SendObject? Hope that makes sense.
 
Last edited:
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 00:29
Joined
Nov 30, 2011
Messages
8,494
Hello Michael,

I am afraid you will not be able to send HTML emails via SendObject method.. However you can use OutputTo method to convert the report into a PDF and the use that PDF as an attachment to send email via an Outlook Object.. Same applies to Signatures..

There are several posts in this forum that will have a working code for emails using VBA.. Post back if you are stuck..
 
M

MichaelWaimauku

Registered User.
Local time
Today, 11:29
Joined
Dec 6, 2012
Messages
57
I've now almost got it done, completely rewritten... however, needs some error handling in it. If I cancel the email rather than sending it, it returns the message "Message sent successfully" - what can be put in that would trap the email not being sent and return a message saying "Operation cancelled by user!!"?? Currently it doesn't recognise that an email is cancelled by the user and assumes all went well.

Private Sub Command26_Click()
Me.Dirty = False

DoCmd.OpenReport "nonconformity", acViewPreview, , "[nonconformid] =" & Me.NonConformID

DoCmd.OutputTo acOutputReport, , acFormatPDF, "C:\Issue Details.pdf", False

Dim olApp As Object
Dim objMail As Object
Dim olMail As Object
On Error Resume Next 'Keep going if there is an error

Set olApp = GetObject(, "Outlook.Application") 'See if Outlook is open

If Err Then 'Outlook is not open
Set olApp = CreateObject("Outlook.Application") 'Create a new instance of Outlook
End If

Set objMail = olApp.CreateItem(olMailItem)
With objMail

.BodyFormat = olFormatHTML
.To = Forms![non conformity]![Contact]

.Attachments.Add "C:/Issue Details.pdf"
.display

Kill "C:/Issue Details.pdf"

DoCmd.Close acReport, "NonConformity", acSaveNo

End With
MsgBox "Message Sent Successfully."
End Sub
 
Last edited:
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 00:29
Joined
Nov 30, 2011
Messages
8,494
Well in the link I have provided earlier "Error Handling in VBA" it would have mentioned the use of Resume Next to be.
It is tempting to deal with errors by placing an On Error Resume Next statement at the top of the procedure in order to get the code to run without raising an error. This is very bad coding practice. Remember that using On Error Resume Next does not fix errors. It merely ignores them.
Click to expand...
However in the scenario of the Outlook object you have to move next if the Outlook is not open.. So in order to achieve this, you have to devise the scenario a bit different as in putting the email procedure in a seperate Sub, this way that error handler will be different..
Code: 
Private Sub Command26_Click()
[COLOR=Black]On Error GoTo errHandler[/COLOR]
    Dim fileName As String
    DoCmd.OpenReport "nonconformity", acViewPreview, , "[nonconformid] =" & Me.NonConformID
    [COLOR=Red]fileName = "C:\Issue Details.pdf"[/COLOR]
    DoCmd.OutputTo acOutputReport, , acFormatPDF, fileName, False
    DoCmd.Close acReport, "NonConformity", acSaveNo
    [COLOR=Red]Call SendEmail(fileName)[/COLOR]
    MsgBox "Message Sent Successfully."
[COLOR=Black]exitOnErr:
    Exit Sub
errHandler:
    errHandler:
    If Err.Number <> 2501 Then
        Call MsgBox("An Error occured !!" & vbCrLf & vbCrLf & Err.Number & " : " & Err.Description, vbCritical)
    Else
        Call MsgBox("Operation canceled by User !!", vbInformation)
    End If
    Resume exitOnError[/COLOR]
End Sub
If you have looked at the code carefully the file name is passed as an argument to the SendEmail Sub this way the SendEmail can be used by several other procedures.. More user friendly the user can select the name of the file.. etc etc.. It all depends on how you wish to make the code more recyclable.. This is the procedure for emails..
Code: 
[COLOR=Red]Private Sub SendEmail(fileName As String)[/COLOR]
    Dim olApp As Object
    Dim objMail As Object
    Dim olMail As Object
    On Error Resume Next         [COLOR=Green]'Keep going if there is an error[/COLOR]

    Set olApp = GetObject(, "Outlook.Application")[COLOR=Green] 'See if Outlook is open[/COLOR]

    If Err Then 'Outlook is not open
        Set olApp = CreateObject("Outlook.Application") [COLOR=Green]'Create a new instance of Outlook[/COLOR]
    End If

    Set objMail = olApp.CreateItem(olMailItem)
    With objMail
        .BodyFormat = olFormatHTML
        .To = Forms![non conformity]![Contact]
        .Attachments.Add fileName
        .Display
    End With
  [COLOR=Green]  'Kill "C:\Issue Details.pdf"[/COLOR]
End Sub
If you also see I have commented out the Kill this is because if one program (Outlook) is using the file you will not be able to delete it.. So based on the Resume Next it will never be deleted.. Unless you want to use .Send before you use Kill, and after which you can delete the file.. So think carefully before you do so..

Hope this clarifies most of your questions.. Post back if you still find it hard to follow..
 
M

MichaelWaimauku

Registered User.
Local time
Today, 11:29
Joined
Dec 6, 2012
Messages
57
Hi Paul

There's so many ways of writing these things!!! :) I copied your code. By commenting out the kill, all that means is the file is constantly overwritten rather than being removed. No issue for me. However, I'd prefer it to be removed as that file will be in everyone's root directory. Also, at the point of .display, the file has already been attached to the email so can be deleted.

The error '2501' is redundant now since I'm not using the SendObject anymore - that error related only to that and not to the now used OutputTo.

However, in the procedure SendEmail, if I remark out On Error Resume Next, the following error occurs: 'Run-time Error '429': ActiveX component can't create object. Only when Outlook not open. If Outlook open, then this error occurs: 5: Invalid procedure call or arguement.

How can I get around this one if I wasn't to have the Resume Next statement?
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 00:29
Joined
Nov 30, 2011
Messages
8,494
No you might have misunderstood me.. I put SendEmail as a separate procedure just because of the reason you have to use Resume Next.. Do not comment out Resume Next.. If you have them together it will cause the Error handler to be in a fix where to go.. Just copy the code above as it is..

Where does the Invalid procedure call Error occur?
 
M

MichaelWaimauku

Registered User.
Local time
Today, 11:29
Joined
Dec 6, 2012
Messages
57
Thanks for clarifying that Paul, thought I wasn't meant to use that. In regards to the errors, I don't know which part of the script they're occuring as it doesn't show up like in the past, just a Msg box occuring. I can only assume it's the result of the commenting out of the Resume Next. When not commented out, it works fine.

Now, the other issue I was having. Because Outlook is called separately from Access, when the email is open and the user would enter info, if the user cancels the email (for whatever reason), it produces no error as there is no error persay. However, the msg that returns 'Email sent' is not true. Not sure if there's a way of dealing with this?
 
Last edited:
M

MichaelWaimauku

Registered User.
Local time
Today, 11:29
Joined
Dec 6, 2012
Messages
57
Also, just noticed that Msg "Message sent Sucessfully" displays in Access even thought the email is still open and hasn't been sent.
 
pr2-eugin

pr2-eugin

Super Moderator
Local time
Today, 00:29
Joined
Nov 30, 2011
Messages
8,494
Hmmm.. I have not actually played around with Outlook Inspector.. But if you research on that you will be able to see how to see if the user has sent it or not to an extent..

I do not have access to MS Access/Outlook right now.. maybe not until the new year, as I will be off for vacation stating tomorrow for Christmas, so if I do not get back to you in this regard, I am sorry.. :(
 
You must log in or register to reply here.

Similar threads

mike60smart
Error 2501
2
Replies
29
Views
883
mike60smart
mike60smart
L
Send looping Email
Replies
3
Views
404
looma123
L
S
Solved sendobject and default email program
Replies
13
Views
521
Pat Hartman
P
A
Report output to filepath variable
Replies
4
Views
467
Antony Cole
A
M
VBA output report to PDF and email
Replies
18
Views
908
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom