Solved

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

Posted on 2008-06-23
11
1,643 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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
 
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server views 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 Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

732 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