Messaging from Excel...

[paddymack]

Hi again, not sure if this is possible but I was wondering if a macro could be written to send a netsend message to a particular PC on the network when a certain sheet is accessed? any thoughts (running NT)

 

[paddymack]

Sorry don't understand what your saying. From an MSDOS prompt the command "net send (userid) message here..." sends a pop up box to that userid's screen. This is what I'm trying to achieve from a macro.

 

[Andrew Poulsom]

Building on Raider's post and my suggestion to use the Shell function, this code will send a message to the current user when the workbook is opened.

Option Explicit

Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, _
Nsize As Long) As Long

Private Sub Workbook_Open()
    Dim Buffer As String * 100
    Dim BuffLen As Long
    Dim FName As String
    Dim FNo As Integer
    Dim Txt As String
    Dim x
    BuffLen = 100
    FName = ThisWorkbook.Path & Application.PathSeparator & "NetSend.BAT"
    FNo = FreeFile
    GetUserName Buffer, BuffLen
    Txt = "NET SEND " & Left(Buffer, BuffLen - 1) & " Hello"
    Open FName For Output As #FNo
    Print #FNo, Txt
    Close #FNo
    x = Shell(FName, 1)
End Sub

 

[paddymack]

Brilliant! I'm really impressed. Not entirely sure what you've done here but It works exactly as I asked for - well done. Would it be possible to ask for a bit of an explanation of whats going on here so I can modify it further for my own needs? Tanks in advance.

 

[Andrew Poulsom]

Well this bit uses a Windows API call to get the Username:

Private Declare Function GetUserName Lib "advapi32.dll" _
Alias "GetUserNameA" (ByVal lpBuffer As String, _
Nsize As Long) As Long

Dim Buffer As String * 100
Dim BuffLen As Long
BuffLen = 100
GetUserName Buffer, BuffLen

at which stage the user name is in the variable Buffer with a length of BuffLen.

This bit sets up some variables, opens a batch file and writes a line of text to it:

'** NAME OF BATCH FILE **
FName = ThisWorkbook.Path & Application.PathSeparator & "NetSend.BAT"
'** NUMBER OF NEXT FREE FILE **
FNo = FreeFile
'** TEXT TO WRITE TO BATCH FILE **
Txt = "NET SEND " & Left(Buffer, BuffLen - 1) & " Hello"
'** OPEN THE BATCH FILE **
Open FName For Output As #FNo
'** WRITE THE NET SEND TEXT TO IT **
Print #FNo, Txt
'** CLOSE THE BATCH FILE **
Close #FNo

And this executes the batch file ie it types the line of text at the Command prompt:

x = Shell(FName, 1)

 

[Haluk]

Andrew;

This is just great !
I'm also impressed with your code.

 

[Swamp Thing]

There is an API that directly provides programmatic access to the NET SEND utility:

http://internettrash.com/users/fdb/broadcas.htm


========== Quote ===
Now, it's a little-known fact, but you can actually access this message sending functionality through the API. And we have all the code you need right here, ready for you to copy-and-paste, allowing you to instantly creating your own mini messenger or office news broadcast system!

To use, call BroadcastMessage, passing in three arguments - a user or machine name, a sender name, plus your actual message. The function will return a True or False dependant on success.

Sample Usage<pre>
x = BroadcastMessage("PYTHON", "Karl", "Boooo! It's me. Time for tea?")</pre>
=============== UNQUOTE ===========
This message was edited by Swamp Thing on 2002-11-12 03:18

 

[phantom1975]

Here's a shot in the dark.

Everyone would have a linked workbook. Have a macro in each workbook that updates the links every 30 seconds. You can then have a Selection_Change event that would pop up a MsgBox with data in a cell with new information. Does this give you an idea?

 

[paddymack]

Ok I know I'm being greedy. Got Andrews code to work great.... but having seen Swamp Things code I'd really like to add a report to this so that I can see if the message delivery was successful or not. But I'm having difficulty in marrying the two codes together, anyone have any advice?