Skip to Content
Author's profile photo Abin Alex

Broadcasting SAP BI Workbooks using vba

Using the below code you can overcome the limitation of BexBroadcaster by sending the content of BW Workbook in body of the mail instead of attachment.

Mail Client Used : Outlook 2010.

Macro features :

  • Automatic Logon to BW using BExAnalyzer.xla
  • Automatically run  BW Workbook on specific time
  • Save the generated BW Workbook to local drive
  • Copy the contents of saved workbook to body of outlook mail
  • Attach the saved workbook in outlook mail (Attachment)
  • Send mail to mentioned recipients(To:,Cc:,Bcc:)

Please modify the below code marked in red  based on your config…


‘**************************SPECIFIC TIME RUN*********************

Private Sub Workbook_Open()

Application.OnTime TimeValue(“07:40:00”), “bwreport”

End Sub

‘**************************SAP LOGON *****************************

Function logonToBW(app)

Dim myConnection  As Object

Dim logonToBW2  As Boolean

On Error Resume Next

logonToBW2 = False

‘ Create logon to system with following user and system details

Set myConnection = app. Run (“BExAnalyzer. xla! SAPBEXgetConnection”)

With myConnection

.client = “723

.User = “it2in

.Password = “xxxxxx

.Language = “en

.systemnumber = “57

.System = “”

.systemid = “BIP

.ApplicationServer = “ip

.SAProuter = “”

.Logon 0, True

‘ If logon fails, bring up text and exit function

If .IsConnected <> 1 Then

MsgBox “Automatic logon failed. Please enter your username and password in the next screen …”

Exit Function

End If

End With

‘ Run connection query to see if connected

app.Run “BExAnalyzer.xla!SAPBEXinitConnection”

logonToBW2 = True

End Function

‘*************************** BW RUN **************************

Sub bwreport()

‘ Launch Excel

Dim strsavepath

Dim app

Set app = CreateObject(“Excel.Application”)

‘ Make it visible

app.Visible = True

Application.DisplayAlerts = False

‘ Open xla file

app.Workbooks.Open (“C:\Program Files\Common Files\SAP Shared\BW\BExAnalyzer.xla“)

‘ Logon onto BW

Call logonToBW(app)

‘app.DisplayAlerts = False

‘ Run the workbook report,specify the workbook id.

app.Run “BExAnalyzer.xla! SAPBEXreadWorkbook”, “4UJ6CHX39BBBNJJHYNJTUEY8

app.Run (“BExAnalyzer.xla! Auto_Close”)

Dim DefPath As String

Dim strDate As String

strDate = Format(Now, ” dd-mm-yy “)

DefPath = “E:\ABIN\Daily\

Set wbdest = app.ActiveWorkbook

Application.DisplayAlerts = False

app.DisplayAlerts = False

wbdest.CheckCompatibility = False

wbdest.SaveAs DefPath & “DailySalesReport-” & strDate, 52


app.Visible = False

Set app = Nothing

Application.DisplayAlerts = True

Call sendmymail

End Sub

‘************************* OUTLOOK MAIL ***************************

Sub sendmymail()

Workbooks.Open (“E:\ABIN\Daily\DailySalesReport-” & Format(Now, ” dd-mm-yy “) & “.xlsm”)

Set mywbk = ActiveWorkbook



Dim objOutlook As outlook.Application

Dim objOutlookMsg As outlook.MailItem

Dim objDoc As Word.Document

Dim objInspector As outlook.Inspector

Set objOutlook = CreateObject(“Outlook.Application”)

Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg

.Subject = “Automated Daily Sales Report:” & Format(Now, ” dd-mmmm-yyyy “)

‘.BodyFormat = olFormatRichText

.To = “xxxxxx @”

.CC = ” zzzzz@ “

.BCC = ” ababaab@ “


End With

Set objInspector = objOutlook.ActiveInspector

If Not objInspector Is Nothing And objInspector.EditorType = olEditorWord Then

        Set objDoc = objInspector.WordEditor


End If



With objOutlookMsg

.Attachments.Add (“E:\ABIN\Daily\DailySalesReport-” & Format(Now, ” dd-mm-yy “) & “.xlsm”)


End With

Set objOutlook = Nothing

Set ol = Nothing

Set olInbox = Nothing

Set objOutlookMsg = Nothing

Set objInspector = Nothing

this.Close False

End Sub

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Raman Korrapati
      Raman Korrapati

      Very useful doc Abin.

      Thanks for sharing and making.

      Thank you


      Author's profile photo Abin Alex
      Abin Alex
      Blog Post Author


      Author's profile photo Manna Das
      Manna Das

      indeed its a very useful document to send reports automatically without the need of Bex Broadcaster.

      Kind Regards

      Manna Das

      Author's profile photo Abin Alex
      Abin Alex
      Blog Post Author

      Thnx !

      Author's profile photo vinod bokade
      vinod bokade

      Thanks for sharing.

      As we know, broadcasting is very robust and very little editing options are available.

      I am broadcasting a workbook of size 10MB, will this code will be helpful to reduce the size. If yes, this will help for not to create WAD pages as per workbook and then broadcast.

      Any idea ?

      Thank-You in advance.



      Author's profile photo Abin Alex
      Abin Alex
      Blog Post Author

      Hi vinod,

      No,the code doesn't do any compression.

      This code can be used to run the workbook and save it in local drive and it will attach the saved file in your outlook mail,so it will be using the outlook data policy.

      Since we are saving the file it is taken out of BW so there won't be any WAD.

      Author's profile photo Former Member
      Former Member

      For anyone who came here and is having trouble figuring out your login variables you can do a manual login, set 'myConnection' to the current connection and then output all the variables to the immediate window or message box.

      Set myConnection = app.Run("BEx Analyzer.xla!SAPBEXgetConnection")

      With myConnection

           Debug.Print "Client " & .client & vbCrLf & _

           "System Number " & .systemnumber & vbCrLf & _

           "System " & .System & _

            "System ID " & .systemid & vbCrLf & _

            "Application Server " & .ApplicationServer

      End With

      Then you can paste those values into the red spaces in the code above. You can also breakpoint after setting myConnection to view the object's members during run time if you're feeling adventurous.

      Edit - I'm using this with sapbex.xla and not bexanalyzer.xla for what it's worth. That's the only place so this code is technically untested.

      Author's profile photo Abin Alex
      Abin Alex
      Blog Post Author

      Thanks for the comment Bryan!,I have used bexanalyzer.xla since my BW version has been upgraded to 7.0 and it is working fine all day .Trust me it's tested,if you are facing any issue in the above code please let me know 🙂 .

      I am using the above code to automatically send the BW report to a list of user's before i come to office 😉 .I am using windows task scheduler to open the excel macro at a particular time and the macro will get triggered when the time is 7:40 am ,so I can be late to office 😀

      Author's profile photo Former Member
      Former Member

      Oh no, nothing wrong with the code and it's very useful! I just couldn't figure out any of my login variables, nobody seemed to know.

      Everything's working now.

      Thanks Abin.