Sending Bloomberg messages with Excel

[jrutt]

Hello,

I imagine that this could be relatively complicated but here it goes anyway. I have a list of people on excel who are automatically emailed by a macro that I already have when their clients contract is expiring. Right now, I have the macros to automatically send these emails via outlook but now I need for it to happen via Bloomberg. I have found some code that MIGHT work for this and I need help integrating it as I am not very good with writing these things. The macros I already have are listed below:

Module 1 (Find people to email):

Sub SendEmails()    Dim FormulaRange          As Range
    Dim NotSentMsg            As String
    Dim MyMsg                 As String
    Dim SentMsg               As String
    Dim MyLimit               As Long


    NotSentMsg = "No"
    SentMsg = "Yes"


    MyLimit = 1


    Set FormulaRange = Sheets("Compiled").Range("P5:P535")


    On Error GoTo EndMacro:
    For Each FormulaCell In FormulaRange.Cells
        With FormulaCell
            If IsNumeric(.Value) = False Then
                MyMsg = "Not numeric"
            Else
                If .Value = MyLimit Then
                    'If .Value > MyLimit Then
                    MyMsg = SentMsg
                    If .Offset(0, 1).Value = NotSentMsg Then
                        Call Mail_with_outlook2
                    End If
                Else
                    MyMsg = NotSentMsg
                End If
            End If
            Application.EnableEvents = False
            .Offset(0, 1).Value = MyMsg
            Application.EnableEvents = True
        End With
    Next FormulaCell


ExitMacro:
    Exit Sub
EndMacro:
    Application.EnableEvents = True
    MsgBox "Some Error occurred." _
         & vbLf & Err.Number _
         & vbLf & Err.Description


End Sub

Module 2 (Send mail via outlook):

Option Explicit

Public FormulaCell As Range




Sub Mail_with_outlook2()
                                                
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strto As String, strcc As String, strbcc As String
    Dim strsub As String, strbody As String


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)


    strto = Cells(FormulaCell.Row, "F").Value
    strcc = ""
    strbcc = ""
    strsub = "Please Contact " & Cells(FormulaCell.Row, "B").Value
    strbody = "Hi " & Cells(FormulaCell.Row, "E").Value & "," & vbNewLine & vbNewLine & _
              "Please contact your client, " & Cells(FormulaCell.Row, "C").Value & "who has not accrued any execution credits in the last two quarters" & _
              vbNewLine & vbNewLine & "Thank you"


    With OutMail
        .To = strto
        .CC = strcc
        .BCC = strbcc
        .Subject = strsub
        .Body = strbody
        .Send
    End With


    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

ThisWorkbook (trigger macros when saving):

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   Call SendEmails
End Sub

The new code that I found to send via Bloomberg is this:

 Private Sub btnSend_Click()

'This code does NOT make use of the API.
'It controls the Bloomberg desk-top application by
'sending commands.
'strongly suspect that Bbg no longer support this.

Dim lngBLP As Long
Dim objRange As Range
Dim objCell As Range
Dim intChars As Integer

Set objRange = Range("MyRange")

lngBLP = DDEInitiate("winblp", "bbk")

'choose screen to work with
Call DDEExecute(lngBLP, "<blp-0>")
'ensure cursor is at starting position
Call DDEExecute(lngBLP, "")<menu>Call DDEExecute(lngBLP, "")<menu>'open Message page
Call DDEExecute(lngBLP, "MSGE<go>")
'fill in 'To' list
Call DDEExecute(lngBLP, "My_Distribution_List<tabr>")
'next line for subject
Call DDEExecute(lngBLP, "MySubject<tabr>")
'add in body
For Each objCell In objRange
Call DDEExecute(lngBLP, objCell.Value & "<down>")
For intChars = 1 To Len(objCell.Value)
Call DDEExecute(lngBLP, "<left>")
Next intChars

Next objCell

'next line for subject
Call DDEExecute(lngBLP, "<go>")
Call DDEExecute(lngBLP, "1<go>")
'add in whatever else is required to actually send the message

'close the 'conversation'
Call DDETerminate(lngBLP)
End Sub

Again, any help integrating this new code into my existing chain would ENORMOUSLY appreciated.

Thanks in advance!!!

Regards,
Jacob</go></go></left></down></tabr></tabr></go></menu></menu></blp-0>