Solved

Access 2003 - Send emails with attachments automatically without email client running

Posted on 2008-06-23
11
1,622 Views
Last Modified: 2011-12-01
I'm still very new to Access, but have reports that are sent out daily using Groupwise 6.5. This is on a Windows 2003 Server.

Right now I have my Novell Groupwise email client running in order for the reports to be sent out. How can I do this so that Groupwise is not required to be running (will be installed of course)? I read up a lot about this and found some code along with the Groupwise API, but I'm not that savvy to incorporate these together.

I have Access databases that just sent out plain text and others that include attachments. I definitely will need the exact code (besides username and other email credentials) to use. Links will not help out much as I'm pretty sure I've scoured most of them by now and still running in circles.

Thanks.
0
Comment
Question by:greyknight17
  • 6
  • 5
11 Comments
 
LVL 38

Accepted Solution

by:
Jim P. earned 500 total points
Comment Utility
We're running GW 7, but as long as you GW Admin allows SMMTP e-mail, the routine below can send just a plain e-mail or go into a full blown combo of both files from disk or attached queries/tables from the database.

I think I commented it enough to have it make sense.

Please ask for any help that you need.
Public Function SendSMTPMail(Subject As String, MsgTxt As String, RecipientEmails As String, _

                Optional RecieveDisplayName As String = "", Optional AttachFile As String = " ", _

                Optional AttachTableQueryReport As String = " ")

 

'Make sure you have a reference set to "SMTP Send Mail for VB6.0"

'(Menu bar --> Tools --> References)

'The list of attachments is expected to be a semi-colon delimted list.

 

Dim strExchangeServer As String

Dim poSendMail As vbSendMail.clsSendMail

 

Dim DB As DAO.Database

Dim RS As DAO.Recordset

Dim SQL As String

 

Dim AtchList As String

Dim Attch As Boolean

 

AtchList = ""

'Build the list of tables or files that are to be sent _

 along withthe e-mail.

 

If Trim(AttachTableQueryReport) <> "" Or Trim(AttachFile) <> "" Then

    Call OutputAttchForSend(AttachTableQueryReport, AttachFile)

    Attch = True

End If

 

'Actually send the e-mail

Set poSendMail = New clsSendMail

    poSendMail.SMTPHost = "SMTP_Server"

    'This can be hardcoded or use something like getusername function _

     to build it on the fly such as _

     poSendMail.from = getusername & "@LibertySavingsBank.com" _

     Note that building it on the fly you would want to modify the _

     FromDisplayName name as well.

    poSendMail.from = "username@domain.com"

    poSendMail.FromDisplayName = "Friendly Name"

    poSendMail.Recipient = RecipientEmails

    poSendMail.RecipientDisplayName = RecieveDisplayName

    poSendMail.Subject = Subject

    If Attch = True Then

        SQL = "SELECT File_Name, File_Date, Transmitted, Delete_Later " & _

            "FROM tbl_Attachments " & _

            "ORDER BY File_Name "

        Set DB = CurrentDb()

        Set RS = DB.OpenRecordset(SQL)

 

        Do Until RS.EOF

            AtchList = AtchList & RS!File_Name & ";"

            With RS

                .Edit

                !Transmitted = True

                .Update

            End With

            RS.MoveNext

        Loop

        

        Set RS = Nothing

        Set DB = Nothing

        

        AtchList = Left(Trim(AtchList), Len(Trim(AtchList)) - 1)

        Debug.Print AtchList

        poSendMail.Attachment = AtchList    ' file attachment(s), optional

    End If

    poSendMail.Message = MsgTxt

    poSendMail.Send

 

    'Delete the files that were created on the fly for sending.

    SQL = "SELECT File_Name, File_Date, Transmitted, Delete_Later " & _

        "FROM tbl_Attachments " & _

        "WHERE Delete_Later = True" & _

        "ORDER BY File_Name "

    Set DB = CurrentDb()

    Set RS = DB.OpenRecordset(SQL)

 

    Do Until RS.EOF

        Kill RS!File_Name

        RS.MoveNext

    Loop

    

    Set RS = Nothing

    Set DB = Nothing

 

End Function

 

Public Function OutputAttchForSend(Optional ExistToAttch As String = "", _

                Optional CreateToAttach As String = "")

            

'This takes a semi-colon deleted list of tables or files to be _

 attached and inserts them into tbl_Attachments. The ExistToAttch _

 are files already on a drive. The CreateToAttach are tables, queries _

 or reports that need to be dumped to disk and then attached.

 

Dim I As Integer

Dim ReportName As String

Dim AttachType As Long

Dim SQL As String

 

'If this is the first run then tbl_Attachments doesn't exist and this calls a _

 function to create the table on the fly.

 

If DCount("Name", "MSysObjects", "Name = 'tbl_Attachments' And Type = 1") = 0 Then

    Call CreateAttachmentsTable

End If

 

SQL = "DELETE * FROM tbl_Attachments"

DoCmd.SetWarnings False

DoCmd.RunSQL SQL, True

DoCmd.SetWarnings True

 

 

CreateToAttach = Trim(CreateToAttach)

 

If Right(CreateToAttach, 1) = ";" Then

    CreateToAttach = Left(CreateToAttach, Len(CreateToAttach) - 1)

End If

 

 

Do Until Len(Trim(CreateToAttach)) = 0

    If InStr(1, CreateToAttach, ";", vbTextCompare) = 0 And Len(Trim(CreateToAttach)) > 0 Then

        ReportName = Trim(CreateToAttach)

        CreateToAttach = " "

    Else

        ReportName = Left(Trim(CreateToAttach), InStr(1, CreateToAttach, ";", vbTextCompare) - 1)

        CreateToAttach = Trim(Mid(CreateToAttach, Len(ReportName) + 2, 150))

    End If

    AttachType = DLookup("Type", "MSysObjects", "Name = '" & ReportName & "'")

    '-32764 = Report

    '1 = Native Acc Table

    '6 = Attached Acc/Excel/Foxpro Tables

    '4 = ODBC Tables

    '5 = Queries

    

    

    DoCmd.SetWarnings False

        If UCase(Dir("C:\TEMP", vbDirectory)) <> "TEMP" Then

            MkDir "C:\TEMP"

        End If

        Select Case AttachType

            Case -32764

                DoCmd.OutputTo acOutputReport, ReportName, acFormatRTF, _

                    "C:\TEMP\" & ReportName & ".rtf", False

                SQL = "INSERT INTO tbl_Attachments (File_Name, Delete_Later) " & _

                    "VALUES( 'C:\TEMP\" & ReportName & ".rtf',True)"

            Case 1, 4, 5, 6

                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ReportName, _

                    "C:\TEMP\" & ReportName & ".xls", True

                    SQL = "INSERT INTO tbl_Attachments (File_Name, Delete_Later) " & _

                        "VALUES( 'C:\TEMP\" & ReportName & ".xls',True)"

            Case Else

                DoCmd.TransferText acExportDelim, , ReportName, _

                    "C:\TEMP\" & ReportName & ".txt", True

                    SQL = "INSERT INTO tbl_Attachments (File_Name, Delete_Later) " & _

                        "VALUES( 'C:\TEMP\" & ReportName & ".txt',True)"

        End Select

        DoCmd.RunSQL SQL, True

        Debug.Print ReportName

    DoCmd.SetWarnings True

    

    Debug.Print CreateToAttach & " -- " & ReportName

Loop

 

ExistToAttch = Trim(ExistToAttch)

 

If Right(ExistToAttch, 1) = ";" Then

    ExistToAttch = Left(ExistToAttch, Len(ExistToAttch) - 1)

End If

 

Do Until Len(Trim(ExistToAttch)) = 0

    If InStr(1, ExistToAttch, ";", vbTextCompare) = 0 And Len(Trim(ExistToAttch)) > 0 Then

        ReportName = Trim(ExistToAttch)

        ExistToAttch = " "

    Else

        ReportName = Left(Trim(ExistToAttch), InStr(1, ExistToAttch, ";", vbTextCompare) - 1)

        ExistToAttch = Trim(Mid(ExistToAttch, Len(ReportName) + 2, 150))

    End If

    

    SQL = "INSERT INTO tbl_Attachments (File_Name, Delete_Later) " & _

        "VALUES('" & ReportName & "',False)"

    DoCmd.SetWarnings False

        DoCmd.RunSQL SQL, True

        Debug.Print ReportName

    DoCmd.SetWarnings True

Loop

 

End Function

 

Public Function CreateAttachmentsTable()

'This function creates a table to store the list of attachments _

 to be sent with an e-mail.

 

 

Dim DB As DAO.Database

Dim TableName As DAO.TableDef

Dim FieldName As DAO.Field

Dim FieldProperty As Property

Dim I As Integer

 

Set DB = CurrentDb()

Set TableName = DB.CreateTableDef("tbl_Attachments")

    

    With TableName

        .Fields.Append .CreateField("File_Date", dbDate)

        .Fields.Append .CreateField("File_Name", dbText, 75)

        .Fields.Append .CreateField("Transmitted", dbBoolean)

        .Fields.Append .CreateField("Delete_Later", dbBoolean)

        .Fields.Append .CreateField("Index_Num")

        .Fields("Index_Num").Type = dbLong

        .Fields("Index_Num").Attributes = dbAutoIncrField

        .Fields("File_Name").AllowZeroLength = True

        .Fields("File_Date").DefaultValue = "Date()"

    End With

   

    DB.TableDefs.Append TableName

    

    DoCmd.SelectObject acTable, "tbl_Attachments", True

End Function

Open in new window

0
 
LVL 15

Author Comment

by:greyknight17
Comment Utility
Thanks for the prompt reply. Still a newbie at this, but I think these are the main parts I should change:

===================================
Set poSendMail = New clsSendMail
    poSendMail.SMTPHost = "SMTP_Server"
    poSendMail.from = "username@domain.com"
    poSendMail.FromDisplayName = "Friendly Name"
    poSendMail.Recipient = RecipientEmails
    poSendMail.RecipientDisplayName = RecieveDisplayName
    poSendMail.Subject = Subject
===================================

Do I need the FromDisplayName & RecipientDisplayName fields? I assume it will automatically use the ones from my account. For the SMTPHost field, do I use the IP address I connect to and port number (ex: 192.168.x.x:1666)?

Where do I edit it to include attachments? I see a handful of lines there but unsure where exactly to insert the attachment names. Most of these are snapshot files if they have attachments. I assume if there are no attachments, we could just leave it as is right?

I'm using Access 2003 (seems to have VB 6.3 there) and there's no SMTP Send Mail for VB6.0 option under Tools --> References.

Thanks.
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
>> For the SMTPHost field, do I use the IP address

SMTP uses Port 25 as the default port. If not then the :1666 should work. I would suggest using DNS to name the server (an A or C Name record) instead of the TCP/IP address. That way if you change/upgrade the server, you don't have to recode. If
---------------------------------------------
poSendMail.SMTPHost = "GrpWiseSrvr:1666"
---------------------------------------------
is how the server is in the app, then if the replacement is on a different IP then you don't have to change a thing on the workstation(s), just use DNS to repoint it.

From your form or interface it would be something like:
SendSMTPMail "Monthly report", "This is the monthly blah blah ...." , "Boss1@MyDomain.com; Boss2@MyDomain.com",,, "C:\MyPath\Report_200806.pdf",

"MonthlyAccessReport1;MonthlyAccessReport2"

Open in new window

0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
>> I'm using Access 2003 (seems to have VB 6.3 there) and
>> there's no SMTP Send Mail for VB6.0

Search under the C:\Program Files\Microsoft Office directory for vbSendMail.dll.

If you find it, it may not be registered. To register it from a command prompt:
REGSVR32 "C:\Program Files\Microsoft Office\....\vbSendMail.dll"

If you don't have it try a change install to office and add additional components, especially the full load of Access. As an example, I know the calendar controls don't load unless you add them.  You can also download it off the internet, just do a search.
0
 
LVL 15

Author Comment

by:greyknight17
Comment Utility
I tried searching for that vbSendMail.dll file to no avail. I have these databases on two servers both running Access 2003 with ALL the features installed for them.

Is this some third party dll file? I found a copy of that dll from the freevbcode.com website. I will try registering it to see if that will work.

For the sample code you provided, are the last two MonthlyAccessReport just report names directly from the database? I guess that's why you are calling it directly unlike that pdf file. Is there any limit on the recipients and how do I include those in CC field instead of To?

One simple question. Most of these were usually done using Macro calls using the SendObject command. Is there any way I can do something similar for a macro as I'm using the Windows Scheduled Task to run these at specified times. I'm not sure how to do this via forms (if possible). Everything is basically automated.

I'm still reading up on Access and still new to this in regards to reports and emailing them out. I was given this task at work which I know is insane given that I haven't played around with Access for over 6 years now. I appreciate your patience with me on this issue.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
>> I found a copy of that dll from the freevbcode.com website.

I didn't realize it, but that is where it came from. We have it on all our machines already. I think it came from some base image we have running.

>> For the sample code you provided, are the last two MonthlyAccessReport

Yes, those would be tables or queries that you want to send. I don't have code offhand to export report snapshots, but I'm sure if you ask, someone can generate it for you.

>> Is there any limit on the recipients .....

I don't think there is a hard limit, but note that some email servers will label a message with too many recipients or CC's as spam. That depends on the Mail Server Admin at locations.

>> and how do I include those in CC field instead of To?

I didn't actually build it with a CC in mind, but if you change the code as below, it should give you the option.

>> Most of these were usually done using Macro calls ......

I try to avoid macros like the plague. If I have to automate a process to fire from a scheduled task, the way I always do it is with a form with the On Open event. Then go into the Tools --> Startup option and set it to display the form. The other nice thing about it, is when you have to do maintenance, all you have to do is hold the shift key down when opening the database and it bypasses the startup.

>> I was given this task at work which I know is insane given that I
>> haven't played around with Access for over 6 years now.

Its like riding a bike. As long as you're willing to try, think a little, we can get your problem solved.


Public Function SendSMTPMail(Subject As String, MsgTxt As String, RecipientEmails As String, _

                Optional RecieveDisplayName As String = "", Optional RecipientEmails As String ="",  Optional AttachFile As String = " ", _

                Optional AttachTableQueryReport As String = " ")
 
 

    poSendMail.RecipientDisplayName = RecieveDisplayName

    poSendMail.CcRecipient = 

    poSendMail.Subject = Subject

Open in new window

vbsendmail-ver-info.jpg
0
 
LVL 15

Author Comment

by:greyknight17
Comment Utility
I will need to revamp all these databases when I'm more familiar with them and Access. Most of these databases have macros since they are set to run at specified times. Is there any way to do this via macros?

That bypass startup trick is great. It's just that I tend to open these a lot and hate to open up one and forgetting to hold down the SHIFT key one day. These emails will be sent to the higher ups in the dept. and they hate receiving duplicate or junk mail.

I'm not 100% sure how I will do this, but I will give it a try anyway. Need to read up more on this in my Access 2003 book. The macros are also calling queries before actually sending out an email with the reports (snapshots) or just a confirmation saying the queries ran successfully.
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
In a macro it would be the RunCode command.

You would have the command line in that, or in a separate VB Module that is called from the macro to send the mail.
0
 
LVL 15

Author Comment

by:greyknight17
Comment Utility
Busy these last few days. I'll give it a try next week.

Thanks.
0
 
LVL 15

Author Comment

by:greyknight17
Comment Utility
Sorry for the delay. I don't have time at all lately to test this out.

I will award you the points nevertheless. Thanks for your taking your time to work on this. I will try to implement this code into Access one day when I'm less busy.

Thanks.
0
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
I hope it works out.

Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now