Emailing attachments from Access

  • Thread starter Thread starter Terry Holland
  • Start date Start date
T

Terry Holland

Not sure if this is the best group for this...

I have an access application that needs to send emails. Some of the emails
will require an attachment (may be more than one). The attachment will
always be from a defined directory.

Im using the code below to send the mail. What I'd like to be able to do is
set the path of the directory that attachments will come from so that when
user clicks Insert File, the file dialog opens in the correct directory.

If there was only ever going to be a single attachment, I could open a File
dialog first and send an attachment path into procedure (I could do this with
multi-file select enabled on dialog but, if the user clicks Insert File on
email window, the initial directory will be incorrect)

tia


Sub SendEmail2()
Dim strMessage As String
Dim strRecipients As String
Dim strSubject As String
Dim strUser As String

strUser = DLookup("user_txt_forename & "" "" & user_txt_surname",
"tblUser", "user_txt_login=""" & CurrentUser & """")

strSubject = GetSettingValue("PurchasingEmailSubject")
strSubject = Replace(strSubject, "<N>", Me.drawingname_gen)
strSubject = Replace(strSubject, "<R>", Me.[Revision number])
strSubject = Replace(strSubject, "<C>", IIf(IsBlank(Me.[P codes]), "No
Code Available", Me.[P codes]))

strMessage = GetSettingValue("PurchasingEmailBody")
strMessage = Replace(strMessage, "<D>", Me.title)

strRecipients = GetSettingValue("PurchasingEmail")

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.To = strRecipients
.Subject = strSubject
.Body = strMessage

'Need to set the path of the directory
'that attachments will come from so that
'when use clicks Insert File, the file dialog
'opens in the correct directory
'.Attachments.Add "D:\Attachments Dir"


.Display True
.Send
End With
End Sub
 
Not sure if this is the best group for this...

I have an access application that needs to send emails.  Some of the emails
will require an attachment (may be more than one).  The attachment will
always be from a defined directory.

Im using the code below to send the mail.  What I'd like to be able to do is
set the path of the directory that attachments will come from so that when
user clicks Insert File, the file dialog opens in the correct directory.

If there was only ever going to be a single attachment, I could open a File
dialog first and send an attachment path into procedure (I could do this with
multi-file select enabled on dialog but, if the user clicks Insert File on
email window, the initial directory will be incorrect)

tia

Sub SendEmail2()
    Dim strMessage As String
    Dim strRecipients As String
    Dim strSubject As String
    Dim strUser As String

    strUser = DLookup("user_txt_forename & "" "" & user_txt_surname",
"tblUser", "user_txt_login=""" & CurrentUser & """")

    strSubject = GetSettingValue("PurchasingEmailSubject")
    strSubject = Replace(strSubject, "<N>", Me.drawingname_gen)
    strSubject = Replace(strSubject, "<R>", Me.[Revision number])
    strSubject = Replace(strSubject, "<C>", IIf(IsBlank(Me.[P codes]), "No
Code Available", Me.[P codes]))

    strMessage = GetSettingValue("PurchasingEmailBody")
    strMessage = Replace(strMessage, "<D>", Me.title)

    strRecipients = GetSettingValue("PurchasingEmail")

    Dim appOutLook As Outlook.Application
    Dim MailOutLook As Outlook.MailItem
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)

    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)

    With MailOutLook
      .To = strRecipients
      .Subject = strSubject
      .Body = strMessage

      'Need to set the path of the directory
      'that attachments will come from so that
      'when use clicks Insert File, the file dialog
      'opens in the correct directory
      '.Attachments.Add "D:\Attachments Dir"

      .Display True
      .Send
    End With
End Sub
Click to expand...

If you want to set just the directory, you can do that with
BrowseFolder API at Access web. Just stuff the return value (the
path) into a string variable, and then you can process the folder. Or
else use the OpenSaveFile API at the same site.
 
Thanks for response.

You suggestion does not address my issue. Maybe I didnt explain clearly
enough.
My access form will have a button labelled 'Email'
When the user clicks this button my Email2 method will execute.
An outlook email will appear on screen with recipients, subject, and body
text prepopulated. The user may then click Insert from the File menu of the
Outlook Email window. It is the initial directory of this dialog that I want
to define because the users dont want to have to browse to the correct
directory.
The default directory of this Outlook Insert File dialog is the last
directory from which an attachment was added to an email.

The code you have suggested fails on two counts
1) it still requires the user to browse to a directory
2) it has no effect of the default directory for an Outlook Insert File dialog

I do appreciate your attempt to assist

Terry

[email protected] said:
Not sure if this is the best group for this...

I have an access application that needs to send emails. Some of the emails
will require an attachment (may be more than one). The attachment will
always be from a defined directory.

Im using the code below to send the mail. What I'd like to be able to do is
set the path of the directory that attachments will come from so that when
user clicks Insert File, the file dialog opens in the correct directory.

If there was only ever going to be a single attachment, I could open a File
dialog first and send an attachment path into procedure (I could do this with
multi-file select enabled on dialog but, if the user clicks Insert File on
email window, the initial directory will be incorrect)

tia

Sub SendEmail2()
Dim strMessage As String
Dim strRecipients As String
Dim strSubject As String
Dim strUser As String

strUser = DLookup("user_txt_forename & "" "" & user_txt_surname",
"tblUser", "user_txt_login=""" & CurrentUser & """")

strSubject = GetSettingValue("PurchasingEmailSubject")
strSubject = Replace(strSubject, "<N>", Me.drawingname_gen)
strSubject = Replace(strSubject, "<R>", Me.[Revision number])
strSubject = Replace(strSubject, "<C>", IIf(IsBlank(Me.[P codes]), "No
Code Available", Me.[P codes]))

strMessage = GetSettingValue("PurchasingEmailBody")
strMessage = Replace(strMessage, "<D>", Me.title)

strRecipients = GetSettingValue("PurchasingEmail")

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.To = strRecipients
.Subject = strSubject
.Body = strMessage

'Need to set the path of the directory
'that attachments will come from so that
'when use clicks Insert File, the file dialog
'opens in the correct directory
'.Attachments.Add "D:\Attachments Dir"

.Display True
.Send
End With
End Sub
Click to expand...

If you want to set just the directory, you can do that with
BrowseFolder API at Access web. Just stuff the return value (the
path) into a string variable, and then you can process the folder. Or
else use the OpenSaveFile API at the same site.
Click to expand...
 
Back
Top