Email Spreadsheet

[JFHBIFF]

Good evening everybody,

I currently have a button on my form that creates a spreadsheet based on a query called "Area". The name of the spreadsheet is based on a field on the current record on the form

Private Sub Command6_Click()
FileName = PathName & Forms!Areas!Area & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Area", FileName, True

End Sub

What I want to do is add a way to email the spreadsheet to a recipient based on a field that contains a recipients email address.

Thanks for your help

 

[pbaldy]

Take a look at SendObject in VBA Help.

 

[JFHBIFF]

Thanks for the reply pbaldy.

I couldn't get sendobject to add the attachemnt. I found some code that would work. The only thing I am having trouble with is adding a subject line to the email and text to the body of the email. I am using transferspreadsheet to update current spreadsheets, then I need to email the attachment. I can't use sendobject because I need to keep the spreadsheet format. Here is the code I have

Private Sub Command8_Click()
On Error GoTo EmailArea_Err
    Set EmailApp = CreateObject("Outlook.Application")
    Set NameSpace = EmailApp.getNameSpace("MAPI")
    Set emailsend = EmailApp.CreateItem(0)
    Dim strFolderPath As String
    Dim SubjectLine As String
    strFolderPath = "C:\Documents and Settings\132323\My Documents\" & "Area " & Me.Area & ".xls"

    DoCmd.TransferSpreadsheet acExport, 8, "Area", strFolderPath, True
    emailsend.To = Me.emailadd
    emailsend.Attachments.Add strFolderPath
    emailsend.send

EmailArea_Exit:
    Exit Sub

EmailArea_Err:
    MsgBox Error$
    Resume EmailArea_Exit

End Sub

 

[pbaldy]

Sorry, I've been out of town. Did you figure out how to add the subject and body?

 

[GaryPanic]

I have just done this myself...

my solution (which might not be the best ) is
create the xls and dump in in a folder

say C\temp\ith a default name mine is cancellation

c\temp\cancellation.xls
get that bit right now I have email set up on thepush of a button to attach xls from above
and it looks att he email address in my form

text8 has a list of emails I need to send

Private Sub Command13_Click()

Dim EmailApp, NameSpace, EmailSend As Object

Set EmailApp = CreateObject("Outlook.Application")
Set NameSpace = EmailApp.GetNamespace("MAPI")
Set EmailSend = EmailApp.CreateItem(0)

EmailSend.To = [Forms]![claimsreporter]![text8]
EmailSend.Subject = "Claims Report for" & " " & [Forms]![claimsreporter]![Text3]
'EmailSend.Message = "h:\email.txt" (THIS FAILED)
EmailSend.Attachments.Add "C:\temp\cancellation.xls"
EmailSend.Display

Set EmailApp = Nothing
Set NameSpace = Nothing
Set EmailSend = Nothing

'Kill "C:\temp\test2.xls"

End Sub

 

[bnw]

Are you trying to send an email to an individual? If you are, where do you put the person's e-mail address in order for it to be send to them?

Look forward to hearing from you soon.

 

[GaryPanic]

EmailSend.To = [Forms]![claimsreporter]![text8]

so in my for I have a field text8

on my list (I have a list box) that selects a contract I want to report on



so Contract A it will populate the field text8 with
mmouse@disneyworld.xxx;dduck@disneyworld.xx;looneytunes@fishheads.co.uk

if i sect Contract b is populats the text8 with mgoose@fish.co.uk etc
the emails are stored inthe same tables as the contrat - it works

i have to send my report A to 16 people
report B to 12
report c to 2
report D to 16
report E to xx

works a treat