Skip to Content

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


To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. 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.

    Regards,

    VB

    (0) 
    1. Abin Alex 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.

      (0) 
  2. Bryan Mohr

    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.

    (0) 
    1. Abin Alex 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 😀

      (0) 
      1. Bryan Mohr

        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.

        (0) 

Leave a Reply