Send email attachment - but not until it exists!

[Big Pat]

Hi,

My code seeks to create a PDF file and then email it to a recipient. Can I get the code to "pause" until the file has been created? At the moment, it returns an error because the file doesn't exist, but that's because I haven't yet had a chance to confirm the filename and location.

I cobbled together the code below from more than one thread. I don't know if having my DIMs part way down is a problem, I know this is generally done first.

Anyway, the part of the code that creates the PDF file is working fine. It opens a dialog box and prompts me for the location and filename. I will ALWAYS use the same name and location.

The two approaches I can think of are:
1. Code the location and filename directly in VBA so that the file is created without a dialog box. Overwrite a pre-existing file if it exists.
2. Make the email part of the code wait until I have confirmed the location and filename, then resume and send the email.

But I don't know how to accomplish either of these!!

Oh...and I have added a supplementary question in the .Body line below, about how to put line breaks in the message.

Thanks,

Private Sub cmdMHReport_Click()

DoCmd.SetWarnings False

DoCmd.OpenQuery "08 Make MH by Month"
DoCmd.OpenQuery "09 MH running total"


Set Application.Printer = Application.Printers("CutePDF Writer")
DoCmd.OpenReport "rptMH"

DoCmd.SetWarnings True

'------------------------------------------------------
'Email part
Dim objOutlook As New Outlook.Application ' outlook object
Dim objMessage As MailItem, strAttach As String ' outlook mail message

strAttach = "C:\Documents and Settings\ryanp2\Desktop\MH Report.pdf"
Set objMessage = objOutlook.CreateItem(olMailItem)
With objMessage
    .To = "someone@somewhere.com"  ' << << << change to real address
    .subject = "MH Report"
    .Body = "Dear Someone+Chr(13) + Chr(10)+ Here's your report"   '<< << <<  Is this the correct way to put separate lines in the body?
    .Attachments.Add strAttach
    .Send
End With
Set objOutlook = Nothing
Set objMessage = Nothing
'----------------------------------------------------------------------------------

End Sub

 

[pr2-eugin]

1. Code the location and filename directly in VBA so that the file is created without a dialog box. Overwrite a pre-existing file if it exists.

Not necessarily, you can create a two modules one will have the Creation where you can use FileDialog to obtain the file location and name.. then at the end of the procedure call for the next Sub to send it to email..

2. Make the email part of the code wait until I have confirmed the location and filename, then resume and send the email.

I have not use the System Pause, but here are some threads on this forum that will give some information on how to use them, but creating two separate modules should make the process a bit smooth i.e give you the time for creation..

Oh...and I have added a supplementary question in the .Body line below, about how to put line breaks in the message.

You can use vbCrLf to add Carriage return and Line feeds.. Also best to use & as a Concatenation operator, other than +..

.Body = "Dear Someone " & vbCrLf & "Here's your report"

Or to avoid all hassle why not use DoCmd.SendObject method?

 

[Big Pat]

Thanks Paul,

So the email part would be a module by itself and then you would include
Call EmailMH in the code for the existing button? I tried that and I got exactly the same result as before i.e. the cmdMHReport sub outputs to PDF and displays the dialog box but the error message pops up behind it at the same time, saying "Can't find this file. Make sure the path and filename are correct." - before I've had a chance to click Save

Where exactly do I call the separate procedure from? I have put Call EmailMH just before the End Sub line - is that right?

Thanks for the clarification on the VbCrLf. I had copied that from elsewhere and I guess it wasn't quite right.

I'll look into the DoCmd.SendObject method when I get a chance as I haven't come across it before.

Pat

 

[pr2-eugin]

What is the method that you use to save the PDF? Are you using FileDialog or DoCmd.OutputTo method?

I would be able to rewrite your code if I could understand the method you use to save the creation of PDF..

 

[Big Pat]

The PDF is created by these lines

Set Application.Printer = Application.Printers("CutePDF Writer")
DoCmd.OpenReport "rptMH"

CutePDF writer is what's known (I think) as a virtual printer i.e. it's installed as a printer and uses print settings. But instead of producing a hard copy, it opens the standard Save As dialog allowing you to specify a filename and location.

I have previously looked at DoCmd.OutputTo a long time ago on another project. I can't remember the problem exactly, but I think I was unable to make it output as a PDF, which is a requirement here.

I can get by, having two separate buttons, one to create the report and one to email it, but it's "dissatisfying" somehow

 

[pr2-eugin]

Pat, just a question.. How would you determine the location of the file that you wish to add? Is it always going to be one location? Or generic?

I have this crazy idea of giving an option to the user, if they wish to attach the file manually or let the system attach after creating the file. This way if the location is already known.. we can use a Do While loop to check if the FileExists.. Then call the Email procedure to send automated email.. this might be a bit of long winded.. but the final call is yours..

 

[Big Pat]

Hi Paul,

As I said in the the first post, I'll *always* use the same filename and location. For now it's the desktop path specified in my original code but I'll change that once I get it working.

This is for a monthly report that I need to send to a specific person outside of our organisation. PDF makes most sense and I'm happy to overwrite the previous file next time, because I don't need to keep a history (and if I did there's always my email archive.)

Also, in this case there's no other user to give options to. I'll be doing this myself and I'm trying to speed the process up by having as few clicks as possible. It's now down to two clicks (and it used to take me a good 30 minutes of copying and pasting to Excel then reformatting borders and charts!) So, overall, I'm pretty pleased with myself.

I looked at the Do While FileExists link you sent and I have to tell you I'd be well in over my head. I understand very little of that! If it's EASY for you to amend that code, then I'm certainly willing to paste it into my database and see what happens. I might learn something. But please don't spend a long time on it.

Thanks,

 

[pr2-eugin]

Sorry Pat, did not look that you mentioned the file path remains the same.. Well this is just my idea.. if some else has a better way of doing it, I hope you will consider it too.. Simply copy the code that Allen Browne has to check for a file's existence into a Module... Then all you have to do is..

Private Sub cmdMHReport_Click()
    DoCmd.SetWarnings False

    DoCmd.OpenQuery "08 Make MH by Month"
    DoCmd.OpenQuery "09 MH running total"

    Set Application.Printer = Application.Printers("CutePDF Writer")
    DoCmd.OpenReport "rptMH"
    
    DoCmd.SetWarnings True
    Do Until [COLOR=RoyalBlue]FileExists[/COLOR]("[COLOR=Red]C:\Documents and Settings\ryanp2\Desktop\MH Report.pdf[/COLOR]")
        DoCmd.Hourglass(True)
    Loop
    DoCmd.Hourglass(False)
    Call EmailMH("C:\Documents and Settings\ryanp2\Desktop\MH Report.pdf")
End Sub

Private Sub EmailMH(strAttach As String)
  [COLOR=SeaGreen]  '------------------------------------------------------
    'Email part[/COLOR]
    Dim objOutlook As New Outlook.Application ' outlook object
    Dim objMessage As MailItem ' outlook mail message
    Set objMessage = objOutlook.CreateItem(olMailItem)
    With objMessage
        .To = "someone@somewhere.com"  [COLOR=SeaGreen]' << << << change to real address[/COLOR]
        .subject = "MH Report"
        .Body = .Body = "Dear Someone " & vbCrLf & "Here's your report"  
        .Attachments.Add strAttach
        .Send
    End With
    Set objOutlook = Nothing
    Set objMessage = Nothing
   [COLOR=SeaGreen] '----------------------------------------------------------------------------------[/COLOR]
End Sub

But also remember that if in case the report does not generate a proper file there is a very high possibility that it will run into a infinite loop.. So think about that..