Auto Email PDF Report Improvement

[jeffshex]

Hey all, I have this piece of code that I put together that loops through a recordset of emails and changes the reports querydefs WHERE clause on each loop for the particular user.
It works and sends out the correct report with the correct info...I'm just looking to do 2 things:

1. I'm not the best VBA writer, so what can I do to cut down on some code or improve it.

2. Keep Outlook from quacking about the "sending on your behalf...allow or deny" message. (I looked into the CDO but am not sure how to get it to send the attachments as smoothly as how I'm doing it now.)
Keep in mind I'm using Access 2007.

Private Sub cmdEmail_Click()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rptqd As DAO.QueryDef
Dim strSQL As String
Dim rst As Recordset

Set db = CurrentDb
Set qd = db.QueryDefs("qryRptItems")
Set rptqd = db.QueryDefs("qryReportSource")

strSQL = qd.SQL

Set rst = db.OpenRecordset("qryEmailList")
    Do Until rst.EOF = True
        strSQL = Left(strSQL, Len(strSQL) - 3) & " WHERE UserName = '" & rst!UserName & "'"
        rptqd.SQL = strSQL
        DoCmd.SendObject acSendReport, "rptItems", acFormatPDF, rst("UserEmail"), , , "Auto Email", "Test", False
        strSQL = qd.SQL
        rst.MoveNext
    Loop
    
rst.Close
                
End Sub

Basically I'm calling a source sql string and trimming the ; off the end of it. I'm then looping through adding a where clause and then sending it off using the SendObject command. Before the loop starts over, I'm resetting my sql to the source again without any where clauses on it.

Thoughts?

 

[lameid]

For a beginner your code looks good. If you really were going down that path (I recomend something a little different below) I probably would not change and initialize (set it back to the default value) the variabale strSQL. I would just set it the one time and set the SQL of the querydef to a concatenated string. This saves accessing the one querydef repeatedly.

I would not take the overhead of changing a query everytime like you are doing.

You can make a text box hidden on a form (I recommend the one that has the button that fires the code) and use it for your criteria in your query.

Then in code just set it. Since it looks like you are using the same form in code you can reference it as ME. I commented out what I would remove.

Private Sub cmdEmail_Click()
Dim db As DAO.Database
'Dim qd As DAO.QueryDef
'Dim rptqd As DAO.QueryDef
'Dim strSQL As String
Dim rst As Recordset

Set db = CurrentDb
'Set qd = db.QueryDefs("qryRptItems")
'Set rptqd = db.QueryDefs("qryReportSource")

'strSQL = qd.SQL

Set rst = db.OpenRecordset("qryEmailList")
    Do Until rst.EOF = True
        'strSQL = Left(strSQL, Len(strSQL) - 3) & " WHERE UserName = '" & rst!UserName & "'"
        'rptqd.SQL = strSQL
        Me!txtUser = rst!UserName
        DoCmd.SendObject acSendReport, "rptItems", acFormatPDF, rst("UserEmail"), , , "Auto Email", "Test", False
        'strSQL = qd.SQL
        rst.MoveNext
    Loop
    
rst.Close
                
End Sub

Your second in surpressing the outlook message is trickier. You can use CDO, Buy a product called redemption which clones the oulook object model but doesn't give the warning, or find code a dll that will send an SMTP message. I think there is also one or more FAQ's on this. In all cases you will likely need to export the file to PDF first and then turn around and e-mail the file. Since I don't have 2007 to play with I don't know how to save to a PDF but I would guess you would use docmd.OutputTo

Almost forgot one solution. Go ahead and use your docmd.sendobject but instead use a different e-mail program as the default (in IE go to tools, internet options and select whatever you install). Outlook is preventing the send not Access. There are many mail clients... Outlook, Outlook Express and Eudora to name a few. The caveat here is that everytime you click on an e-mail enabled link or anything else that trys to automate e-mail you will get the other program not Outlook. You can use Outlook but it will at least the first time ask you if you want to make it the default. No is the correct answer if you want your code to work. This method is rather irritating but may good for doing it in a pinch.

 

[jeffshex]

Ok, makes good sense.

1. As far as using the text box approach...
Would I be editing the reports OnOpen event to apply a filter and point it to the form or do I just set the parameter in the query to look for the text box? I think either which way will work, but which is more appropriate?

2. I only will send out automated reports maybe once a month to about 7 people...so I could probably get by with clicking allow every 5 seconds for the 7. (It's another story once the list of people gets larger.) I'm sure I can figure out a way to dump the report to a directory, attach and email it, then just overwrite the same file without a prompt using the CDO approach, but that code looks kind of advanced for me at the moment.

The Office 07 PDF feature is pretty quick and snappy - I like it. The file sizes are nice and small too.

 

[lameid]

1. Use the parameter of a query. This way your query can be compliled. Also setting a filter on the report is accessing on object which is what I was trying to avoid.

2. Delete a file...

I think there is a stament called kill but it I think I read about it being depricated "soon" working with 2003. The below should work.

Sub DeleteFile(strFile As String)
    On Error Resume Next
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    FSO.DeleteFile (strFile)
    Set FSO = Nothing
End Sub

Personally I never got far enough into CDO to say one way or another. Are you looking at one of the FAQ's?

 

[jeffshex]

I looked through the FAQ's and found one or two, but this post peaked my interest.

http://www.tek-tips.com/viewthread.cfm?qid=1458464

I'm guessing that this CDO code would be placed in the same spot as the other in the button click event?
Or would it be in a module?

Do I need to check a reference to a certain dll file?

These are the things that I'm not sure of.

 

[lameid]

The click event is part of the forms module. I would probably put the code in its own module and call the procedure / function from the click event. That way it is easy to use from other screens and modify.

Access knows some references... I've not used CDO so I don't know. If you can use createobject with out an error message then no, you don't need a reference.

 

[NXMold]

I wrote a function for sending email with a variable to choose Outlook or SMTP. I use SMTP for system messages that I don't want the user to cancel or even be aware of, outlook is nice because it lets the user review and modify the message before sending.

Maybe I should put this in an FAQ, but all I did was copy/paste from the existing ones so its nothing new really.

Function DBSendMail(sendto, sendcc, subject, messagebody, attachmentpath As String, SMTPMode As Boolean)
'Must associate libraries: Tools, references "Microsoft Outlook object library" and "Microsoft CDO"
On Error GoTo err

If SMTPMode = True Then GoTo SMTP

   Dim objOutlook As Outlook.Application
   Dim objOutlookMsg As Outlook.MailItem
   Dim objOutlookRecip As Outlook.Recipient
   Dim objOutlookAttach As Outlook.Attachment

   Set objOutlook = CreateObject("Outlook.Application")
   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
   With objOutlookMsg
     .To = sendto
     .CC = sendcc
     .subject = subject
     .Body = messagebody
     If Len(attachmentpath) > 1 Then Set objOutlookAttach = .Attachments.Add(attachmentpath)
     .Display 'turns control over to the user - does not send automatically
   End With
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing

Exit Function
SMTP:
    Dim objCDOMail As Object
    Set objCDOMail = CreateObject("CDO.Message")

    objCDOMail.To = sendto
    objCDOMail.FROM = DB_Var(3) 'DB_Var is a function used to call static database variables from a table
    If Len(sendcc) > 1 Then objCDOMail.CC = sendcc
    objCDOMail.subject = subject
    objCDOMail.TextBody = messagebody
    If Len(attachmentpath) > 1 Then objCDOMail.AddAttachment ("file://" & attachmentpath)
    
    objCDOMail.configuration.Fields.Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL] = 2
    objCDOMail.configuration.Fields.Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL] = DB_Var(16)
    objCDOMail.configuration.Fields.Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpauthenticate")[/URL] = 0
    'objCDOMail.configuration.Fields.Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusername")[/URL] = "username"
    'objCDOMail.configuration.Fields.Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendpassword")[/URL] = "password"
    objCDOMail.configuration.Fields.Item("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL] = 25
    objCDOMail.configuration.Fields.Update    'Always run update to set your config
    
    objCDOMail.Send
    Set objCDOMail = Nothing

Exit Function
err:
   MsgBox err.Description

End Function

 

[NXMold]

To expand on that, your code would be something like:

Sub btnReport_OnClick

   '(code to print reports to file)
   'use environment varialbes for the path, like:
    toPath = Environ("USERPROFILE") & "\Desktop\Shortcut.lnk"

   dbsendmail "to", "cc", "subj", "message", topath, true

   '(code to delete the file)

end sub

 

[jeffshex]

Looks pretty snappy NXMold!
I will see if I can incorporate it with my loop that sends an individualized report out to each email in my specified recordset.

Thanks!
-Jeff

 

[jeffshex]

What is the DB_VAR(3) and DB_VAR(16)?
It seems to choke on these. Says Sub or Function not defined.

Also,
Do you have authentication turned off in this example?
I'm guessing so since the username and password is commented out.

Do you need somewhere to enter an SMTP server IP/Name?

Thanks!

 

[lameid]

His code comments say that DB_Var is a funtion that looks up values in a table. It must be his way of storing information like the sender (From = DB_VAR(3)) and the SMTP Server (= DB_Var(16)). I believe this answers your other question... VBA code is relatively intuitive. Please try reading and understanding what you are using.

 

[NXMold]

Yes, DB_Var() is a function I have to retrieve relatively static variables, rather than hard-code them. Just delete the DB_Var(3) and replace with "name@host.com", delete DB_Var(16) and replace with server address such as "192.168.0.1"

I commented out all the SMTP authentication bits, if you need to authenticate then un comment them.

 

[jeffshex]

Thanks! I just got it working as of yesterday.
Had some odd error messages, and then it looked like it sent but was never received.
I ended up finding a site that covers a hand full of different CDO scenarios and was able to gain some more insight on what I needed to know.

Thanks for everyone's help!