Advice - send these reports from within Access or do something else?

keun · Dec 24, 2014

I have a report that I run which is grouped on Organization Name. I have some VBA (thanks to this forum) which splits this report into individual reports for each Organization and drops the reports into a folder. Each report is named with the Organization Name. Let's call this Step 1.

So now I end up with a bunch of reports in a folder (hundreds of reports). I need to email each report to the respective organization. As part of Step 1 I create a table of each Organization Name which is used as part of the looping and file-naming criteria. I can imagine a scenario where I connect an email address to each Organization in the table so that after the report is PDFed it gets emailed to the address. Note that I said I can IMAGINE this scenario. I have no idea if Access is the tool for this job.

However I can also see a scenario where I take some other program and use it to send the PDFs. I can even put the PDFs on an FTP server and email each organization a link to their PDF (security issues aside) - I can use a simple mail merge for this.

The simplest solution is for me to insert the little email doodad into the following code. You tell me how to do that I will not just give you a tek-tips star, I will give you a real-life star.

Code:

Private Sub Command3_Click()

  Const Folder = "C:\path\"
  Const Domain = "qrySPU04ListOfOrgs"
  'Domain can be table name, query name, or sql statement that provides the values to loop
  Const LoopedField = "MinOfID"
  Const NewFileName = "UserOrg"
  Const ReportName = "rpt_qrySPU03"
  
  Dim rs As DAO.Recordset
  Dim LoopedFieldValue As Long
  Dim FileName As String
  Dim FullPath As String
  Dim strWhere As String
  Dim NewNewFileName As String
  Set rs = CurrentDb.OpenRecordset(Domain)
 
  Do While Not rs.EOF
    LoopedFieldValue = rs.Fields(LoopedField)
    NewNewFileName = rs.Fields(NewFileName)
    FileName = NewNewFileName & ".PDF"
    'The field may be a text field. It then must be surrounded in singlequotes. If so uncomment below
    'LoopedFieldValue = "'" & LoopedFieldValue & "'"
    FullPath = Folder & FileName
    strWhere = LoopedField & " = " & LoopedFieldValue
    Debug.Print FullPath
    Debug.Print strWhere
    DoCmd.OpenReport ReportName, acViewPreview, , strWhere
    DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FullPath
    DoCmd.Close acReport, ReportName
    rs.MoveNext
  Loop
End Sub

I joined this forum in 2005. I am still a hack.

Andrzejek · Dec 24, 2014

You could use Access' DoCmd to send an Outlook e-mail message, or you could by-pass Outlook and send an e-mail this way:

Add reference to: Microsoft CDO for Windows 2000 Library

Code:

Public Sub SendAMessage(strFrom As String, strTo As String, _
    strCC As String, strSubject As String, strTextBody As String, _
    Optional strBcc As String, Optional strAttachDoc As String, _
    Optional blnHighPriority As Boolean = False)

Dim objMessage As CDO.Message

On Error GoTo MyErrorHadler

Set objMessage = New CDO.Message

With objMessage
    .From = strFrom
    .To = strTo
    If Len(Trim$(strCC)) > 0 Then
        .CC = strCC
    End If
    If Len(strBcc) > 0 Then
        .BCC = strBcc
    End If[green]
    ''' On behalf of
    '.Sender = "Bob.Wolf@msn.com"
    [/green]
    If blnHighPriority Then
       With .Fields[green]
           ' for Outlook:[/green]
           .Item(cdoImportance) = cdoHigh
           .Item(cdoPriority) = cdoPriorityUrgent
    [green]
           ' for Outlook Express:
           '.Item("urn:schemas:mailheader:X-Priority") = 1
    [/green]
           .Update
       End With
    End If
    
    .Subject = strSubject
    
    If InStr(UCase(strTextBody), "<HTML>") Or InStr(UCase(strTextBody), "</HTML>") Then
        .HTMLBody = strTextBody
    Else
        .TextBody = strTextBody
    End If

    If Len(strAttachDoc) > 0 Then
        .AddAttachment strAttachDoc
    End If
    
    With .Configuration.Fields
        .Item(CDO.cdoSMTPServer) = "[red]YourSMTPServerHere[/red]"
        .Item(CDO.cdoSMTPServerPort) = 25
        .Item(CDO.cdoSendUsingMethod) = CDO.cdoSendUsingPort
        .Item(cdoSMTPConnectionTimeout) = 10
        .Update
    End With
    .Send
End With

Set objMessage = Nothing

Exit Sub
MyErrorHadler:

End Sub

Just call this Sub wherever you want to send an e-mail, pass some parameters and you are done.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

Andrzejek · Dec 24, 2014

Yout "little email doodad" would go here:

Code:

    ...
    DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FullPath
    DoCmd.Close acReport, ReportName
[blue]
    Call SendAMessage("keun@MyBank.com", "SomeCompany@world.com", "", _
        "Your report", "Hello.  Your report (attached)", "", FullPath)
[/blue]
    rs.MoveNext
  Loop
End Sub

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

PHV · Dec 24, 2014

Have a look at the DoCmd.SendObject method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Advice - send these reports from within Access or do something else?

keun

Technical User

Andrzejek

Programmer

Andrzejek

Programmer

PHV

MIS

Similar threads

Part and Inventory Search

Sponsor