Solved

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

Posted on 2008-06-23
11
1,630 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
ID: 21860403
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
ID: 21861460
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.
ID: 21866163
>> 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.
ID: 21870830
>> 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
ID: 21880186
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 38

Expert Comment

by:Jim P.
ID: 21880309
>> 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
ID: 21888136
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.
ID: 21894389
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
ID: 21929203
Busy these last few days. I'll give it a try next week.

Thanks.
0
 
LVL 15

Author Comment

by:greyknight17
ID: 22032338
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.
ID: 22034810
I hope it works out.

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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

863 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

24 Experts available now in Live!

Get 1:1 Help Now