Help with VB macro to email reports.

system · August 23, 2005, 4:06pm

I am looking for ideas, code, etc for a problem I have. We use publisher to schedule reports to be emailed…and as some of you may know…publisher is crap and doesn’t work most of the time.

So…I am looking for some ideas, help, vb code etc, for a macro that will email the reports we want to the individuals we want. My VB scripting experience is very limited and that is why i need help.

Any help would be greatly appreciated.

Thanks!!

Soupcan75 (BOB member since 2005-08-23)

SebastienGoiffon · August 23, 2005, 4:09pm

Hi again sorry but do not make cross post !

Read the simple rules here

Admin will clean your posts !

Hope someone could help you

Regards

system · August 24, 2005, 12:03am

This code creates the PDF file:

Application.ActiveDocument.ExportAsPDF “\Servername\Directoryname\PDFfilename”

Note: do not include the .pdf extension in the PDFfilename

You can then use something such as the DOS “Shell” function within a .bat file to email it out. Your DBA should be able to help with this.

jac (BOB member since 2005-05-10)

system · September 12, 2005, 8:20am

I use BCA in combination with BLAT. (http://www.blat.net/)

example macro:

Public Sub SaveAndSend()

Dim Doc As Document
Dim Result As Double
Dim Command As String

Path = “E:\export” 'path to where report is being saved
ExtensieXLS = “.xls” 'example is excelformat

Set Doc = ActiveDocument

Doc.SaveAs Path & Doc.Name & “_” & Format(Date, “yyyymmdd”) & ExtensieXLS
'saving report as NAME_20050912.xls

RptName = Doc.Name

Command = "E:\bo65\Thirdparties\blat240\blat.exe - -body ““Do not reply, this emailadress will not be read!”” -subject "“some text - " & RptName & “-” & Format(Date, “yyyymmdd”) & “”” -server server.name.com:25 -u accountname -pw password -f bogus@mail.com -to user@mail.com ,moreusers@mail.com -attach " & Path & RptName & “_” & Format(Date, “yyyymmdd”) & ExtensieXLS
'make sure that this is on ONE line in macro!!
'after command is the path where u install Blat!

Result = Shell(Command, 1)
End Sub

In BCA just refresh and run macro.

Works perfect here

GL

Rene (BOB member since 2005-04-26)

system · September 12, 2005, 10:44am

Is ur issue resolved ?
The rep. can be emailed directly to different email addresses by using a Macro in the report. Schedule the rep. in BCA. When the rep. runs the macro gets executed and will send the rep as excel,pdf,text etc formats to the email address present in the Macro.
If u want the code for macro then do let me knw…
Hav a Good Day…

Anand Sahu (BOB member since 2005-08-03)

system · September 12, 2005, 2:19pm

I would love the code. I downloaded a sample report with code and added my email to the code etc. and it worked with that report. But when I pasted the code into a new report…it errored off on the first line of code.

Thanks!!

Soupcan75 (BOB member since 2005-08-23)

system · September 15, 2005, 7:08am

hi,

even i am facing the same problem.
please send the code to me if possible…

thanks in advance,

Hitesh Amin

hitesh_amin (BOB member since 2005-09-14)

system · September 28, 2005, 3:18pm

Hy,

I tried your vba in a .rep. It work’s fine (I succeeded in sendin a mail hrough the VBA … with attached file …)

However, when I send the .rep in the BCA, ask to refresh and to execute the macro, nothing happens ?

Are-there specifics rights to implement in the Console in order to use BCA on the BCA ?

I do not understand.

Thank’s for your answer.

Ty_Bou (BOB member since 2005-02-16)

system · September 29, 2005, 7:19pm

It’s O.K. It works fine now. The pb was a due to bad rights on the file system.

Ty_Bou (BOB member since 2005-02-16)

system · October 7, 2005, 4:23pm

Hi Listers,

 I try the below Blat code and it tells me Can't Assign read only property. 

 Can anyone help me in identifying what mistake I am doing here.

Thanks,

K…

Karthik_sub30 (BOB member since 2005-06-27)

system · May 10, 2006, 8:26am

I’d really appreciate a copy of the vba code that automates emailing of scheduled reports from windows server using outlook. I have an urgent requirement to email a report in Excel format.

bocis (BOB member since 2003-04-09)

system · May 10, 2006, 8:47am

Hi,
I am giving you the code to send email through bca or whenever you run your report.
Follow the below steps:
Open the report;
click on >> Macro >> Macros.
Create a new Macro and attach the below given codes.
Also in the code give the smtp server your cmpy is using.( in last but 4 line). Also change the server port according to your cmpy.
Creat a folder ClientReports under C drive. and run the report.

Hope this work out for you…

Let me know of any issue.

Dim strComputer As String
Dim strClientName As String
Dim strClientID As String
Dim objFSO
Dim objTextFile
Dim strResponses As String
Dim strEmailAddrs As String
Dim objEmail
Dim strEmailSubject As String
Dim strAttachPath As String
Dim strAttachFileName As String
Dim strAttachFile As String
Dim strEmailBodyText As String
Dim strReportType As String
Dim Doc As Document
Dim Rpt As Report
Dim I As Integer
Dim j As Integer
Dim xlsApp As Object
Dim strICount As String

Private Sub Document_AfterRefresh()
Init
End Sub

'***************************************************************************************
'Master sub for getting the email started
'***************************************************************************************
Sub Init()
Const ForReading = 1

strAttachPath = “c:\ClientReports”
strReportType = “excel”

'***************************************************************************************
'Only edit this section of the script.
'Put the email addresses between the quotes in this line strEmailAddrs
’ i.e. Ram@ramayana.com ;sita@ramayana.com

’ REPORT FORMAT IS EXCEL

'Put the subject of the email between the quotes in this line strEmailSubject
'Put the text of the email between the quotes in this line strEmailBodyText
'strICount is the amount of data that the report will contain
strEmailAddrs = “Ram@ramayana.com ;sita@ramayana.com”
strEmailSubject = “Hello Baby”
strEmailBodyText = “Attached is the Report.”
strICount = 500

'***************************************************************************************

GetReportType

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set xlsApp = CreateObject(“Excel.Application”)
’ xlsApp.Visible = False
’ xlsApp.Interactive = False

xlsApp.Workbooks.Open Filename:=strAttachFile

'DelRowCol
xlsApp.Range(“A1”).Select
xlsApp.ActiveWorkbook.Save
xlsApp.ActiveWindow.Close
xlsApp.Workbooks.Close
xlsApp.Quit
Set xlsApp = Nothing
SendClientEmail
End Sub

'***************************************************************************************
'Sub for getting the report type and exporting as that type
'***************************************************************************************
Sub GetReportType()
Select Case UCase(strReportType)
Case “EXCEL”
ExportDocAsEXCEL
End Select
End Sub

'***************************************************************************************
'Sub for Exporting Report as Excel (.XLS)
'***************************************************************************************
Sub ExportDocAsEXCEL()
Set Doc = ActiveDocument

For I = 1 To Doc.Reports.Count
Set Rpt = Doc.Reports.Item(I)
Rpt.ExportAsEXCEL (strAttachPath & Rpt.Name & “1.xls”)
Next I
strAttachFile = (strAttachPath & Rpt.Name & “1.xls”)
End Sub

'***************************************************************************************
'Sub for sending email with report as attachment
'***************************************************************************************
Sub SendClientEmail()
Set objEmail = CreateObject(“CDO.Message”)
objEmail.From = “BusinessObjects”
objEmail.To = strEmailAddrs
objEmail.Subject = strEmailSubject
objEmail.TextBody = strEmailBodyText
objEmail.AddAttachment (strAttachFile)
objEmail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/sendusing”) = 2
objEmail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserver”) = “yourserver.com”
objEmail.Configuration.Fields.Item(“http://schemas.microsoft.com/cdo/configuration/smtpserverport”) = 25
objEmail.Configuration.Fields.Update
objEmail.Send
Set objEmail = Nothing
End Sub

Anand Sahu (BOB member since 2005-08-03)

system · May 10, 2006, 3:00pm

Many thanks for the code … I have copied the code, but have run into issues towards the bottom end. I receive compilation error for the objEmail.Send line

bocis (BOB member since 2003-04-09)

system · May 10, 2006, 3:06pm

Let me know the error messge…
Also be sure to put in your smtp server port and server name…

Anand Sahu (BOB member since 2005-08-03)

system · May 10, 2006, 3:41pm

It returns the following error

Run-Time error -'2147220980 (8004020c)
At least one recipient is required, but none were found

bocis (BOB member since 2003-04-09)

system · May 11, 2006, 1:44pm

Dumb question, but does it run with just ONE email address? If so, have you tried commas to seperate the names?

Bump: I checked my macros, and they all use commas to seperate the addresses.

B

bdouglas (BOB member since 2002-08-29)

system · May 20, 2006, 10:16am

Can you send me the macro. I want to automate sending reports to multible e-mail adresses.

regarts,

Roger van Lith

rvanlith (BOB member since 2006-05-20)

system · May 22, 2006, 3:16pm

Send Mail via VBA

If you search this forum, you’ll also see that there are several other working examples, as well…

DJ

DJ06482 (BOB member since 2002-11-22)

system · August 3, 2006, 2:49pm

can anyone send the script for mailing a rep as a PDF file like the one for excel

sivaram (BOB member since 2005-11-22)