Scott said:
I have a macro that creates an excel spreadsheet with 5 or
6 tabs of information. It saves the spreadsheet on my C
drive
How would I use the macro to then take that spreadsheet
and email it to people?
Hi Scott,
The following should give you a good starting point I believe.
It has several caveats:
1) uses early binding which typically is no-no,
but has worked for me in situations
where not a multiple/shared db...so can set
references to Outlook and DAO
and not worry about it.
2) assumes you have a table where you
store email addresses of people you
want to email your excel file to
(stored as Text, not Hyperlink)
and you manipulate a query "qryEMail"
based on this table to return those addresses
in a field "EMail"
Create a new module (say "modEMail")
and cut and paste following code into it.
Change "qryEMail" to the name of your query,
and "EMail" to the name of field in query that
contains email addresses in function SendMsg.
'***** start of code ******************
Public Function SendMsg()
On Error GoTo Err_SendMsg
Dim appOutlook As New Outlook.Application
Dim itm As Outlook.MailItem
Dim strExcelPath As String
Dim strToMe As String
strExcelPath = "C:\somedir\myfile.xls" 'replace w/ actual path
strToMe = (e-mail address removed) 'replace w/ your email address
'Create new mail msg
Set itm = appOutlook.CreateItem(olMailItem)
With itm
.To = strToMe
.BCC = GetEMailAddresses("qryEMail", "EMail")
.Subject = "The Excel file" 'replace w/ your subject
.Body = "Here be the Excel file." 'replace w/your Body msg
.Attachments.Add strExcelPath
'display msg before hitting Send
.Display
End With
Exit_SendMsg:
Exit Function
Err_SendMsg:
MsgBox Err.Description
Resume Exit_SendMsg
End Function
Function GetEMailAddresses(pQueryName As String, _
pFieldName As String) As String
On Error GoTo Err_GetEMailAddress
Dim rs As DAO.Recordset
Dim varTemp As Variant
varTemp = ""
Set rs = CurrentDb.OpenRecordset(pQueryName)
rs.MoveFirst
Do While Not rs.EOF
varTemp = varTemp & "<" & rs.Fields(pFieldName) & ">, "
rs.MoveNext
Loop
'remove ending comma and space
GetEMailAddresses = Left(varTemp, Len(varTemp) - 2)
'Debug.Print varTemp
rs.Close
Exit_GetEMailAddress:
Set rs = Nothing
Exit Function
Err_GetEMailAddress:
MsgBox Err.Description
Resume Exit_GetEMailAddress
End Function
..*** end of code ***************
I've never used a macro ever, so here be
how I would run this code:
Then put a command button on some form.
Give it a caption like "Send Message"
In its properties, next to line "OnClick"
type in the following:
=SendMsg()
{possibly you could put this function call
in your macro...but I have no experience here}
Please respond back if I have misunderstood
or was not clear about something.
(Outlook code was adapted from
http://www.woodyswatch.com/access/archtemplate.asp?4-07)
Good luck,
Gary Walter
