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…

CODE :

‘**************************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

wbdest.Close

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

ActiveWorkbook.Sheets(“Sheet1“).Activate

Sheets(“Sheet1”).Range(“A2:H20“).Copy

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 @ yyyy.com”

.CC = ” zzzzz@ yyyy.com “

.BCC = ” ababaab@ yyyy.com “

.Display

End With

Set objInspector = objOutlook.ActiveInspector

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

        Set objDoc = objInspector.WordEditor

        objDoc.Range.Paste

End If

  mywbk.Save

mywbk.Close

With objOutlookMsg

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

.Send

End With

Set objOutlook = Nothing

Set ol = Nothing

Set olInbox = Nothing

Set objOutlookMsg = Nothing

Set objInspector = Nothing

this.Close False

End Sub