• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

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

 
0
DavidHBD
Asked:
DavidHBD
  • 4
  • 3
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now