auto email reports in CSV format

[GJP55]

Is it possible to auto email a report in csv format. I have numerous reports in excel, snapshot and rtf going out but would like to do the same for a report in CSV ??

 

[EliseFreedman]

a similar question has been asked on google newsgroups several times.

A simple search on google yielded the following

Reports cannot be saved to csv files. This isn't a limitation of Access, it's inherent in the concepts "report" and "csv file".

A report can include headings, groupings, summary lines, total lines, page breaks, sub-reports, and much more, all formatted for output on paper or on screen.

A csv file can only contain data records. Each line of data in the csv file represents one row in a table or query (except for the optional first line of field names). Every line must contain exactly the same fields. There is no scope for any grouping, totals, character or paragraph formatting, or any of the other features that characterise reports.

Taking this into account, the answer to your question is NO, you cannot Auto-Email a report in CSV Format. You can, however, e-mail the query/table which is the underlying recordset for the report. To do this you would have to use the Transfer Text command

 

[GJP55]

I do the transfer text command and send the report to a folder then manually email it at the moment so I guess I will have to live with it until we migrate to SQL in a few months.

Thanks for your reply though, at least I can stop looking now !

Cheers

 

[mp9]

If you've got as far as exporting the file you want to mail, check out

http://www22.brinkster.com/accessory/modules/005.shtml

for a way of programmatically auto-mailing a file from within your VBA (as long as you're using Outlook).

HTH.

 

[TLady]

I was affraid to click Download
on this site you, mp9 recommending.
I've been annoyed by the message too, if I AM the one sending e-mails why should I be "secured", right ?
Have anyone tried it ?
How does it seems ?

http://www.dimastr.com/redemption/

TIA

 

[SeeThru]

Yup, you can...takes a bit of work...

In a module, select Tools, references, and add a reference to Outlook.

Create the query, possible using run-time options.
Programatically output the query as a text file to an outside text file, naming the text file .csv.
(NB if the query includes memo fields, you will have to use a append query to make a table of the query, and then output the table)

Create a public function something like this....

Function SendOutlookMessage(Recipients As String, Subject As String, Body As String, DisplayMsg As Boolean, Optional CopyRecipients As String, Optional BlindCopyRecipients As String, Optional Importance As Integer = 2, Optional AttachmentPath)
'Function to create and send an outlook message with more control than sendobject
'separate multiple recipients or CC, or BCC with comma
'importance - 1=low, 2=normal, 3=high

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.recipient
Dim objOutlookAttach As Outlook.Attachment
Dim txtRecipient As String

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application&quot

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg
' Add the To recipient(s) to the message.
Do While InStr(1, Recipients, &quot;,&quot;, vbTextCompare) <> 0 'checks for multiple recipients and adds each
txtRecipient = Left(Recipients, InStr(1, Recipients, &quot;,&quot;, vbTextCompare) - 1)
Recipients = Trim(Mid(Recipients, Len(txtRecipient) + 2, Len(Recipients)))
Set objOutlookRecip = .Recipients.Add(txtRecipient)
objOutlookRecip.Type = olTo
Loop

Set objOutlookRecip = .Recipients.Add(Trim(Recipients))
objOutlookRecip.Type = olTo

' Add the CC recipient(s) to the message if existing
If CopyRecipients <> &quot;&quot; Then
Set objOutlookRecip = .Recipients.Add(CopyRecipients)
objOutlookRecip.Type = olCC
End If

' Add the BCC recipient(s) to the message.
If BlindCopyRecipients <> &quot;&quot; Then
Set objOutlookRecip = .Recipients.Add(BlindCopyRecipients)
objOutlookRecip.Type = olBCC
End If

' Set the Subject, Body, and Importance of the message.
.Subject = Subject
.Body = Body & vbCrLf & vbCrLf
Select Case Importance
Case 1
.Importance = olImportanceLow
Case 2
.Importance = olImportanceNormal
Case 3
.Importance = olImportanceHigh
Case Else
.Importance = olImportanceNormal
End Select

' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

' Should we display the message before sending?
If DisplayMsg Then
.Display
Else
.Save
.Send
End If
End With
Set objOutlook = Nothing

End Function


Call this function from your code, using the final option attachmentpath, to include your file.....


Am email will be created - you have the option of editing it or not before it is sent.

HTH