Attachments

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
 
G

Guest

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

Guest

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

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Email Subject Line 12
Attachments 1
Invalid use of null 1
Dlookup Frustration 2
Need help with looping through records to email report 7
Help with VB 5
outlook 2007 warning message when emailing from Access 2
Qualifier Must Be Collection 1

Top