受欢迎的博客标签

Excel VBA如何给员工发短信工资信息(Microsoft.XMLHTTP)

Published

如何给员工发短信工资信息如何给员工发短信工资信息。本方法借助使用中国网建SMS短信通平台接口,给员工发送短信。

Excel的VBA代码如下:

Public Function getHtmlStr(strUrl As String) ‘获取远程接口函数
On Error Resume Next
Dim XmlHttp As Object, stime, ntime
Set XmlHttp = CreateObject(“Microsoft.XMLHTTP”)
XmlHttp.Open “GET”, strUrl, True
XmlHttp.send
stime = Now ‘获取当前时间
While XmlHttp.ReadyState <> 4
DoEvents
ntime = Now ‘获取循环时间
If DateDiff(“s”, stime, ntime) > 3 Then getHtmlStr = “”: Exit Function
Wend
getHtmlStr = StrConv(XmlHttp.responseBody, vbUnicode)
Set XmlHttp = Nothing
End Function
Sub SendMsg()
If MsgBox(“确认发送短信吗?”, vbYesNo, “请选择”) = vbYes Then
Dim UseId As String, Key As String, Mob As String, Text As String, a As String, split As String
UseId = “ABC” ‘平台账号的UseId
Key = “abcdefghi123456789” ‘平台交换的Key值
For r = 4 To Sheet1.Range(“A10000”).End(xlUp).Row
‘设置接收手机号码
Mob = Sheet1.Range(“ac” & r)’AC列放接收短信的手机号码
‘设置接收内容
For c = 1 To 28
split = split & Sheet1.Cells(3, c) & Sheet1.Cells(r, c) & “,”      ‘第三行开始为发送内容
Next
split = Left(split, Len(split) – 1)
Text = split
split = “”
‘ Debug.Print Text
a = getHtmlStr(“http://sms.webchinese.cn/web_api/?Uid=” & UseId & “&Key= ” & Key & “&smsMob=” & Mob & “&smsText=” & Text & “”) ‘获取接口返回值
Select Case a
Case -1
a = “发送失败,没有该用户账户”
Case -2
a = “发送失败,接口密钥不正确”
Case -21
a = “发送失败,MD5接口密钥加密不正确”
Case -3
a = “发送失败,短信数量不足”
Case -11
a = “发送失败,该用户被禁用”
Case -14
a = “发送

vbs

用VBS发送短信(飞信)

m = "xxxyyyyzzzz"  '手机号码
pass = "12345678"  '登陆密码
msg = "Hello world" '飞信内容
Const online = 1 '在线
Const busy = 2   '忙碌
Const away = 3   '离开
Const hidden = 4 '隐身
Dim http
Set http = CreateObject("Msxml2.XMLHTTP")
http.open "POST", "http://f.10086.cn/im/login/inputpasssubmit1.action", False
http.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
http.send "m=" & m & "&pass=" & pass & "&loginstatus=" & hidden '隐身登陆
wml = http.responseText
If InStr(wml, "密码输入错误") Then
    WScript.Echo "对不起,密码输入错误,请重新输入!"
    WScript.Quit '登陆失败,退出程序
End If
http.open "POST", "http://f.10086.cn/im/user/sendMsgToMyselfs.action", False
http.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
http.send "msg=" & msg '给自己的手机发短信
wml = http.responseText
If InStr(wml, "发送成功") Then WScript.Echo "发送成功"
http.open "GET", "http://f.10086.cn/im/index/logoutsubmit.action", False
http.send '注销登陆