Broadcasting SAP BI Workbooks using vba

[former_member218336]

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