Attach files to outgoing emails from within access
Posted on 2003-03-10
From within my database I can create emails and place them in outlook 98 outbox for sending. I can also specify whether a specific file needs to be attached. Unfortunately, I now need to be able to include one or many files, for example a couple of word documents, a spreadsheet and a powerpoint file. Names and file types can be different each time.
Below is the code I currently use which sets in motion the email creation.
Private Sub cmdSendEmail_Click()
On Error GoTo cmdSendEmail_ClickError
Dim Msg As String
intreturned = MsgBox("Send e-mail to Warden selected. If you continue," & Chr(13) & Chr(10) _
& "1 e-mail messages will be queued for sending!", vbOKCancel + vbCritical + vbDefaultButton2, "Send E-mail")
Select Case intreturned
If IsNull(Me.fraAttachment) Then
If Me!fraAttachment = "1" Then
SendMessage False, "c:\Resident\Notice.doc"
If Me!fraAttachment = "2" Then
SendMessage False, "c:\Resident\Notice.txt"
If Me!fraAttachment = "3" Then
MsgBox error$, 16, RSS
and an excerpt from the SendMessage function
Sub SendMessage(DisplayMsg As Boolean, Optional AttachmentPath)
On Error GoTo SendMessageError
Dim strDisplayText As String
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim dbs As Database, rst As Recordset
further dims here but not relevant ....
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add(rstW!Email)
objOutlookRecip.Type = olTo
.Subject = [txtSubject]
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
' Should we display the message before sending?
If DisplayMsg Then
I have excluded a lot of the code from within the SendMessage function because it is not relevant and is very long.
So the guts of the question. I need to be able to retrieve any number of files (could be doc, xls, ppt or others) from C:\RESIDENT\ and attach them to the outgoing email. One thought is that a listbox shows the contents of c:\resident and some sort of loop goes thru the listbox attaching each of the files in turn to the email being created.
Any suggestions ?? Very grateful for any solutions that can be provided!!