Attachments

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Question - Does any one know how to add an attachment such as Excel to the
code outlined below? I really would not like to deviate to much from the
code below. Thanks in advance!




Function ForecastnotificationFC(strOpCo As String, strEmail As String) As
Boolean
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = strEmail
strSubject = strOpCo
strMessageBody = DLookup("Body", "tbleforecastmessage")
DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False
ForecastnotificationFC = True
PROC_EXIT:
Exit Function
PROC_ERR:
ForecastnotificationFC = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function


Private Sub Command43_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strEmail As String
Dim strOpCo As String
Dim fOk As Boolean
strSQL = "SELECT tblforecastnames.OpCo, tblforecastnames.Email From
[tblforecastnames] Where Oustanding = -1"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Do While Not rst.EOF
strOpCo = rst.Fields("OpCo")
strEmail = rst.Fields("Email")
DoEvents
fOk = ForecastnotificationFC(strOpCo, strEmail)
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " & strEmail
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
 
Thank you Daniel for pointing me in right direction!

Daniel said:
SendObject does not allow the use of external attachment (only access
objects). To do what you want, you'll need to automate Outlook. Take a look
at

http://msdn2.microsoft.com/en-us/li...odc_ac_olauto_sendanoutlookmessageusingaccess

For some sample code to get you on your way.

--
Hope this helps,

Daniel P





Stacey said:
Question - Does any one know how to add an attachment such as Excel to the
code outlined below? I really would not like to deviate to much from the
code below. Thanks in advance!




Function ForecastnotificationFC(strOpCo As String, strEmail As String) As
Boolean
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = strEmail
strSubject = strOpCo
strMessageBody = DLookup("Body", "tbleforecastmessage")
DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False
ForecastnotificationFC = True
PROC_EXIT:
Exit Function
PROC_ERR:
ForecastnotificationFC = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function


Private Sub Command43_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strEmail As String
Dim strOpCo As String
Dim fOk As Boolean
strSQL = "SELECT tblforecastnames.OpCo, tblforecastnames.Email From
[tblforecastnames] Where Oustanding = -1"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Do While Not rst.EOF
strOpCo = rst.Fields("OpCo")
strEmail = rst.Fields("Email")
DoEvents
fOk = ForecastnotificationFC(strOpCo, strEmail)
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " & strEmail
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Click to expand...
Click to expand...
 
Daniel, how do you automate it so that users can change who the email goes to
with continuously modifying the code? ....if I were to go the "automate
Oulook" route.

Daniel said:
SendObject does not allow the use of external attachment (only access
objects). To do what you want, you'll need to automate Outlook. Take a look
at

http://msdn2.microsoft.com/en-us/li...odc_ac_olauto_sendanoutlookmessageusingaccess

For some sample code to get you on your way.

--
Hope this helps,

Daniel P





Stacey said:
Question - Does any one know how to add an attachment such as Excel to the
code outlined below? I really would not like to deviate to much from the
code below. Thanks in advance!




Function ForecastnotificationFC(strOpCo As String, strEmail As String) As
Boolean
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = strEmail
strSubject = strOpCo
strMessageBody = DLookup("Body", "tbleforecastmessage")
DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False
ForecastnotificationFC = True
PROC_EXIT:
Exit Function
PROC_ERR:
ForecastnotificationFC = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function


Private Sub Command43_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strEmail As String
Dim strOpCo As String
Dim fOk As Boolean
strSQL = "SELECT tblforecastnames.OpCo, tblforecastnames.Email From
[tblforecastnames] Where Oustanding = -1"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Do While Not rst.EOF
strOpCo = rst.Fields("OpCo")
strEmail = rst.Fields("Email")
DoEvents
fOk = ForecastnotificationFC(strOpCo, strEmail)
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " & strEmail
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Click to expand...
Click to expand...
 
You'd have to link that input variable to a form entry.

Have a control on your form where the user enters the TO email address and
then call it's value in the code (Me.ControlName).
--
Hope this helps,

Daniel P





Stacey said:
Daniel, how do you automate it so that users can change who the email goes to
with continuously modifying the code? ....if I were to go the "automate
Oulook" route.

Daniel said:
SendObject does not allow the use of external attachment (only access
objects). To do what you want, you'll need to automate Outlook. Take a look
at

http://msdn2.microsoft.com/en-us/li...odc_ac_olauto_sendanoutlookmessageusingaccess

For some sample code to get you on your way.

--
Hope this helps,

Daniel P





Stacey said:
Question - Does any one know how to add an attachment such as Excel to the
code outlined below? I really would not like to deviate to much from the
code below. Thanks in advance!




Function ForecastnotificationFC(strOpCo As String, strEmail As String) As
Boolean
On Error GoTo PROC_ERR
Dim strRecipient As String
Dim strSubject As String
Dim strMessageBody As String
strRecipient = strEmail
strSubject = strOpCo
strMessageBody = DLookup("Body", "tbleforecastmessage")
DoCmd.SendObject acSendNoObject, , , strRecipient, , , strSubject,
strMessageBody, False
ForecastnotificationFC = True
PROC_EXIT:
Exit Function
PROC_ERR:
ForecastnotificationFC = False
If Err.Number = 2501 Then
Call MsgBox( _
"The email was not sent for " & strEmail & ".", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"User Cancelled Operation")
Else
MsgBox Err.Description
End If
Resume PROC_EXIT
End Function


Private Sub Command43_Click()
On Error GoTo PROC_ERR
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strEmail As String
Dim strOpCo As String
Dim fOk As Boolean
strSQL = "SELECT tblforecastnames.OpCo, tblforecastnames.Email From
[tblforecastnames] Where Oustanding = -1"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
Do While Not rst.EOF
strOpCo = rst.Fields("OpCo")
strEmail = rst.Fields("Email")
DoEvents
fOk = ForecastnotificationFC(strOpCo, strEmail)
If Not fOk Then
MsgBox "Delivery Failure to the following email address: " & strEmail
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox Err.Description
Resume PROC_EXIT
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top