I need assistance in creating some code to generate a report using acrobat with access 2007

Can someone please tell me the smartest way to email a report say, each week automatically as access is turned off at the end of the day

We use outlook exchange server 2003  ...outlook...

i know how to convert into acrobat etc and the report is of course created but the final step...automating has got me stumped

My guess is that someone out there can send me a few lines of code bearing in mind that my expertise is not as smart as some...i would assume that i would go to event procedure in the form that we close of from each day and enter code at this point..i have managed over time with some lines of code for other operation and i am a survivor and always manage to "get there in the end" any help from you clever folk would be appreciated

 
DavidHBDAsked:
Who is Participating?
 
Kelvin SparksCommented:
Hi

Sorry for the delay - away visiting clients all day.

I'd suggest as form closes it checks if the days is a Tuesday or Thursday, if so run the routine below. This code should be in the Forms On Close Event

You'll have to build functionality to create the path & filename for the pdf and the strings used for sTo,sCopy (comment out if not needed), sSubject and sBody

I haven't added an error handler. This is object based, so no need for references to Outlook to be set.


Kelvin


If Weekday(Date, vbSunday) = 3 Or Weekday(Date, vbSunday) = 5 Then
    Dim sSQL As String
    Dim sTo As String
    Dim sCopy As String
    Dim sSubject As String
    Dim sBody As String
    Dim sAttachment As String
    Dim db As DAO.Database
    Dim rsData As DAO.Recordset
    Dim objOutlook As Object
    Dim objOutlookMsg As Object
    
    sAttachment = "C:\Dummy\Reportname.pdf"
    
    ''This will create the report as pdf and save it
    DoCmd.OutputTo acOutputReport, "rptName", acformatpdf, sAttachment, False
    
    ''Now do the email
    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
    
    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(0)
    
    With objOutlookMsg
        .To = sTo
        .CC = sCopy
        .Subject = sSubject
        .Body = sBody
        .Attachments.Add sAttachment
        .Save
        .Display
    End With
    Set objOutlook = Nothing
End If

Open in new window

0
 
Kelvin SparksCommented:
I'm guessing that the form only closes at the end of the day. You speak of each week and then every day. Does the email have to be generated daily or weekly (and if weekly on a given day of the week)?

Generating an email and attaching a pdf is fairly straight forward - probably in the OnClose Event for the form

Advise what you want & I can probaly give you the base code needed. As you'll use Outlook & Outlooko security is a pain, I usually prepare the email, attach the attachments and then open it for viewing in Outlook. If all is well, then click the send button and it's gone.


Kelvin
0
 
DavidHBDAuthor Commented:
Kelvin....

Thanks for your reply

we will send, say every Tuesday and Thursday, and yes, i agree the operator will close down Access at which point he will then check outlook to see that the email has been prepared and then will hit "send"

The main thing is that I am wanting to avoid the operator having to go through the process of opening the report ,converting to pdf and then sending.AND forgetting to actually generate the email...often I am overseas and want to see reports, nothing worse than waiting and not receiving.

Thanks again for your interest......incidentally I was considering crystal reports as a solution but could not link back to access, file extension in 2007 is no longer mdb...I think your solution is best
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DavidHBDAuthor Commented:
Thanks for your help Kelvin

Battling through.... bearing in mind that when it comes to visual basic i am a novice

Getting a complie error....End If without block if

Not sure that i know what you mean by "I haven't added an error handler. This is object based, so no need for references to Outlook to be set."

to test that this will work i have temporarily removed the "if statement " ...first line of code

Thanks again for your assistance, please dont go to to much trouble as you will be a busy guy

Regards

David Fealy

0
 
Kelvin SparksCommented:
Did you copy the End if in the very last line of the snippet?

Error handler is to manage any error that might happen, recommended but not essential. If you know know about references, then that won't worry you.


Kelvin
0
 
DavidHBDAuthor Commented:

Hi, notice first row removed (?)

Private Sub Form_Close()

    Dim sSQL As String
    Dim sTo As String
    Dim sCopy As String
    Dim sSubject As String
    Dim sBody As String
    Dim sAttachment As String
    Dim db As DAO.Database
    Dim rsData As DAO.Recordset
    Dim objOutlook As Object
    Dim objOutlookMsg As Object
   
    sAttachment = "\\Hbdserver\hardware by design\HBD SYSTEM\November database\rptcostingreport.pdf"
   
    ''This will create the report as pdf and save it
    DoCmd.OutputTo acOutputReport, "rptcostingreport", acFormatPDF, sAttachment, False
   
    ''Now do the email
    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
   
    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItem(0)
   
    With objOutlookMsg
        .To = "david@hardwarebydesign.com.au"
        .Subject = "sales Summary"
        .Body = "refer attachment"
        .Attachments.Add sAttachment
        .Save
        .Display
    End With
    Set objOutlook = Nothing
End If
   
   
End Sub
0
 
Kelvin SparksCommented:
Hi

Seems I missed your last post.

Delete the end if  (2nd to last line)


Kelvin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.