Attachments

macca2153

New Member
Joined
Feb 11, 2008
Messages
18
I have created a function which creates emails for various staff. The emails contain three attached files which initially appear in the body of the email. I have selected the Display option so that I can review the emails before sending. So I click the Options and Plain Text on the Outlook Toolbar so that the files are transferred to the top of the email in the Attachments box.
I would like to know if I can use code so that I do not have to perform these manual steps to move the files befor sending the emails as I have a lot of emails to adjust?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,704
Office Version
  1. 2016
Platform
  1. Windows
Have you looked at your macro section and search the arguments for send object.
 

HiTechCoach

Well-known Member
Joined
Aug 29, 2010
Messages
1,008
I have created a function which creates emails for various staff. The emails contain three attached files which initially appear in the body of the email. I have selected the Display option so that I can review the emails before sending. So I click the Options and Plain Text on the Outlook Toolbar so that the files are transferred to the top of the email in the Attachments box.
I would like to know if I can use code so that I do not have to perform these manual steps to move the files befor sending the emails as I have a lot of emails to adjust?


How are you creating the email message? If with VBA code, please post the code. If using Outlook automation you will need to change teh Body type.
 

macca2153

New Member
Joined
Feb 11, 2008
Messages
18
I am using VBA code. Refer below.

Public Sub Emails()
'Variable declaration.
Dim db As Database, rst As Recordset, driver As String, email As String, qdf As QueryDef
Dim rsta As Recordset, strSQL As String, strQueryName As String, strESubject As String
Dim strCustEmail As String, strBody As String
Dim OutApp As Object, OutMail As Object
'Dim qdf As QueryDef
Set db = CurrentDb

'Run Driver Email Addresses which produces a dynaset of drivers and their email addresses.
DoCmd.OpenQuery "DRIVER EMAIL ADDRESSES", acViewNormal, acReadOnly
strQueryName = "Vehicles"
'Initialise values.
Set rst = db.OpenRecordset("DRIVER EMAIL ADDRESSES", dbOpenDynaset)
rst.MoveFirst
'Loop through Driver Email Address table to capture Driver and Email Address for production of cars held
'by that driver and to create an email using that address.
Do
driver = rst("Driver")
email = rst("Email")
' Refresh QueryDefs collection.
db.QueryDefs.Refresh
'Debug.Print driver, email


'Determine cars allocated to the driver
'While Not rst.EOF
strSQL = ("SELECT DECLARATIONS.DRIVER,DECLARATIONS.REGO, DECLARATIONS.DATEFROM, DECLARATIONS.DATETO " & _
"FROM DECLARATIONS " & _
" WHERE (((DECLARATIONS.DRIVER) = """ & driver & """));")
'Wend
'Debug.Print rsta!Rego, rsta!DateFrom, rsta!DateTo

Set qdf = CurrentDb.QueryDefs(strQueryName)
qdf.SQL = strSQL
DoCmd.OutputTo acOutputQuery, "Vehicles", acFormatXLS, "D:\TAX\CARS\Vehicles.XLS"
'******* end query def update ********

strESubject = "FBT Pool Car Declarations YTD September 2011"
'during testing override the cust email destination with your email
strCustEmail = driver

strBody = "All fringe benefits provided to staff must be reported against the employee's APS number to facilitate the reporting of certain benefits on employee group certificates under the Fringe Benefit Tax Reporting Legislation." & vbCrLf & vbCrLf & _
"According to the SAPFLEET system, the pool cars on the attached Vehicles file have been provided to your area for the period indicated. Where the vehicle has been home garaged during the period, an FBT Pool Car Allocation declaration should be completed indicating the apportionment of the private use among employees so the above FBT requirements can be met. Where the vehicle has been garaged overnight at an AP facility or commercial car park during the period, a No Private Usage Declaration should be completed indicating where the vehicle was garaged." & vbCrLf & vbCrLf & _
"Please note only one declaration is required for each vehicle shown below." & vbCrLf & vbCrLf & _
"Blank declarations are attached for your use. The appropriate declaration(s) should be completed for each vehicle listed, scanned and emailed to the Tax Helpline by 8th November, 2011. A hard copy should be forwarded where scanning facilities are not available. It is recommended that you retain a copy for your own records. It should be noted that the percentage allocations amongst the users MUST sum to 100%." & vbCrLf & vbCrLf & _
"Transport branch should be advised promptly of all changes of custodianship of pool cars to ensure that these requests will be accurate." & vbCrLf & vbCrLf & _
"Regards" & vbCrLf & vbCrLf & _
"David McIlveen" & vbCrLf & _
"Tax Analyst" & vbCrLf & _
"Australia Post" & vbCrLf & _
"Financial Control & Governance - Finance & Business Services" & vbCrLf & _
"Level 4, 219 - 241 Cleveland Street, Strawberry Hills, NSW, 1427" & vbCrLf & vbCrLf & _
"P 02 9202 6324 | F 02 9202 6887" & vbCrLf & _
"E david.mcilveen@auspost.com.au | W auspost.com.au"




'create the email w/ attached rtf of query
'last parameter allows editing of the email before sending
'to send without viewing email use: False
'DoCmd.SendObject acSendQuery, strQueryName, acFormatRTF, strCustEmail, , , strESubject, strBody, True

'move to next record
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.Subject = strESubject
.To = strCustEmail
.Body = strBody
.Attachments.Add ("D:\TAX\CARS\Vehicles.XLS")
.Attachments.Add ("D:\TAX\CARS\FRINGE BENEFITS POOL CAR ALLOCATION DECLARATION.DOC")
.Attachments.Add ("D:\TAX\CARS\No Private Usage Declaration Car.DOC")
.Display

End With
rst.MoveNext
Loop Until rst.EOF
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,808
Members
416,884
Latest member
leeshjay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top