邮件群发不同邮件发不同人从excel利用notesmass mailingdiffere.docx
《邮件群发不同邮件发不同人从excel利用notesmass mailingdiffere.docx》由会员分享，可在线阅读，更多相关《邮件群发不同邮件发不同人从excel利用notesmass mailingdiffere.docx（8页珍藏版）》请在冰点文库上搜索。
邮件群发不同邮件发不同人从excel利用notesmassmailingdiffere
邮件群发-不同邮件发不同人（从excel,利用notes）（Massmailing-differentmessagesfromdifferentpeople（fromexcel,usingnotes））
保存到草稿箱（sub）
Dimnosessiontheobject,nodatabasetheobject'nosession?
?
nodatabase?
?
Dimnodocumenttheobject,noattachmenttheobject'nodocument?
?
noattachment?
?
DimI%,fileself$J$stmsgtheinteger','I=邮件数量stmsg=.Body邮件信体fileselfJ
Dimvarecipient（）thestring'varecipient=.Sendto邮件地址
Dimvafiles（）thestring'vafiles附件地址和路径
Dimstmsg（）thestring'修改原程序,将邮件信体设置为字符串数组
Dimstsubject（）thestring'修改原程序,将邮件标题设置为字符串数组
DimHSHStheshape'
'stmsg="best®ards"&vbcrlf&_
'application.username&vbcrlf&_
'vbcrlf&_
"--------------------------------------------------------------------------"&vbcrlf&_
'"（重要文件,因涉及个人奖金评价,请重视.）"&vbcrlf&_
'"（填写完毕后,请及时上交给我,谢谢.）"
'****取消原程序用窗体控件的部分,改为用特征字列
'I=0
'ReDimvarecipient（activesheet.shapes.count-1）thestring'邮件地址数组的大小=活动勾选框-1
'ReDimvafiles（activesheet.shapes.count-1）thestring'附件地址和路径数据数组的大小=活动勾选框-1
'ReDimstmsg（activesheet.shapes.count-1）thestring'邮件信体数组的大小=活动勾选框-1
'ReDimstsubject（activesheet.shapes.count-1）thestring'邮件主题数据的大小=活动勾选框-1
'
ForeachHSinactivesheetshapes.
IfHS.Type=msoformcontrolthen'msofromcontrol=窗体控件
Ifsh.formcontroltype=xlcheckboxthen'xlcheckbox=复选框
Ifsh.controlformat.value=1then'找出勾选的邮件地址
'varecipient（I）=（0,1）sh.bottomrightcell.offset勾选框右边第一列为邮件地址
'vafiles（I）=sh.bottomrightcell.offset（0,2）勾选框右边第二列为附件地址和路径
'stsubject（I）=sh.bottomrightcell.offset（0,3）勾选框右边第三列为邮件标题
'stmsg（I）=vbcrlf&sh.bottomrightcell.offset（0,4）&vbcrlf'勾选框右边第四列为邮件信体
'I=I+1
Endif'
Endif'
Endif'
'next
Lastrow=range（"a65536"）.End（xlup）.Row'查找范围
Erow=0
ForI=1tolastrow'计算发送邮件的有效数量
Ifcells（I,a）="发送thenerow=erow+1
Next
ReDimvarecipient（erow-1）thestring'邮件地址数组的大小=有效数量-1
redimvafiles（erow-1）asstring'附件地址和路径数据数组的大小=有效数量-1
redimstmsg（erow-1）asstring'邮件信体数组的大小=有效数量-1
redimstsubject（erow-1）asstring'邮件主题数据的大小=有效数量-1
i=0
forj=1tolastrow
ifcells（j,"a"="发送"then
varecipient（i）=cells（i,b）邮件地址
vafiles（i）=cells（i,c）附件地址和路径
stsubject（i）="&cells（j,d）邮件标题
stmsg（i）=vbcrlf&cells（j,e）邮件内容
i=i+1
endif
next
ifi=0thenmsgbox"没有邮件需要发送":
exitsub'没有勾选,结束程序
'redimpreservevarecipient（i-1）asstring'原程序就已注释掉
'vafiles=application.getopenfilename（filefilter:
="filefiler（*.*）**"title="attachfilesforoutgoinge_mail"multiselect:
=true）原程序就已注释掉
'ifnotisarray（vafiles）thenexitsub'原程序就已注释掉
setnosession=createobject（"notes.notessession"）
setnodatabase=nosession.getdatabase（"",""）
ifnodatabase.isopen=falsethennodatabase.openmail
forj=0to1,-1循环发送
setnodocument=nodatabase.createdocument
setnoattachment=nodocument.createrichtextitem（"body1"）
withnoattachment
.embedobjectembed_attachment","vafiles（j）添加附件
endwith
withnodocument
type="memo".
.sendto=varecipient（j）添加邮件地址
stsubject.subject=（j）添加邮件标题
stmsg.body=（j）添加邮件信体
'.savemessageonsend=true'发送保存
'.posteddate=now（）立即发送?
?
'send'立即发送?
?
callnodocument.save（true,false）
endwith
setnodocument=nothing
nextj
setnodatabase=nothing
setnosession=nothing
appactivate"microsoftexcel"
msgbox"邮件保存完毕",vbinformation
endsub
sub直接发送（）
dimnosessionasobject,nodatabaseasobject'nosession?
?
nodatabase?
?
dimnodocumentasobject,noattachmentasobject'nodocument?
?
noattachment?
?
dimi%,fileself$,jasinteger'stmsg$,i=邮件数量stmsg=.
BodymailbodyFileSelfJ
DimvaRecipient（）AsString'vaRecipient=.sendtomailaddress
Dim,vaFiles（）,As,String,'vaFiles,attachments,addresses,andpaths
DimstMsg（）AsString'modifiestheoriginalprogramtosetthemailbodytostringarray
DimstSubject（）AsString'modifiestheoriginalprogramandsetsthetitleofthemessagetoanarrayofstrings
Dim,SH,As,Shape,'sh
'stMsg="Best&Regards&vbCrLf&_
'Application.UserName&vbCrLf&_
'vbCrLf&_
"--------------------------------------------------------------------------"&vbCrLf&_
Pleasenotethatimportantdocumentsinvolvepersonalbonusevaluations."）&vbCrLf&_
"（pleasefillitouttomeintime,thanks）.）"
'****canceltheoriginalprogramformwiththecontrolpartischangedintothecharactercolumn
"I=0"
'ReDimvaRecipient（ActiveSheet.Shapes.Count-1）AsString'mailingaddressarraysize=activecheckbox'-1'
'ReDimvaFiles（ActiveSheet.Shapes.Count-1）AsString'attachmentaddressandpathdataarraysize=activecheckbox'-1'
'ReDimstMsg（ActiveSheet.Shapes.Count-1）AsString'sizeofmailbodyarray=activecheckbox-1
'ReDimstSubject（ActiveSheet.Shapes.Count-1）AsString'mailsubjectdatasize=activecheckbox-1
'
'For,Each,SH,In,ActiveSheet.Shapes
'Ifsh.Type=msoFormControl,Then,'msofromcontrol=formscontrol
'Ifsh.FormControlType=xlCheckBox,Then,'xlcheckbox=checkbox
'Ifsh.ControlFormat.Value=1Then'findstheselectedmailingaddress
`vaRecipient（I）=sh.BottomRightCell.Offset（0,1）'tickbox,thefirstcolumnontherightisthemailingaddress
`vaFiles（I）=sh.BottomRightCell.Offset（0,2）',tickthebox,andthesecondcolumnontherightistheattachment,address,andpath
`stSubject（I）=sh.BottomRightCell.Offset（0,3）',tickthebox,andthethirdcolumnontherightistheheaderofthemessage
`stMsg（I）=vbCrLf&sh.BottomRightCell.Offset（0,4）&vbCrLf'tickbox,thefourthcolumnontherightisthemailbody
'I=I+1
'EndIf'
'EndIf'
'EndIf'
'Next
Lastrow=Range（"A65536"）,.End（xlUp）.Row"searchrange
Erow=0
"ForI=1,To,lastrow"calculatesthevalidnumberofmessagessent
IfCells（I,"A"）="send"Thenerow=erow+1
Next
ReDimvaRecipient（erow-1）AsString'mailingaddressarraysize=validnumber-1
ReDimvaFiles（erow-1）AsString'attachmentaddressandpathdataarraysize=validnumber-1
ReDimstMsg（erow-1）AsString'sizeofmailbodyarray=validnumber-1
ReDimstSubject（erow-1）AsString'mailsubjectdatasize=validnumber-1
I=0
ForJ=1,To,lastrow
IfCells（J,"A"）="send"Then
varecipient（in）=cells（j,"b"）邮件地址
vafiles（in）=cells（j,"c"）附件地址和路径
stsubject（in）=cells（j"d"）邮件标题
stmsg（in）=vbcrlf&cells（j,"e"）邮件内容
i=i+1
thanif
sync
ifi=0thenmsgbox"没有邮件需要发送":
exitsub'没有勾选,结束程序
'redimpreservevarecipient（-1）asstring'原程序就已注释掉
'vafiles=application.getopenfilename（filefilter:
="filefiles（*.*）**",title:
="attachfilesforoutgoinge_mail",multiselect:
=true）原程序就已注释掉
'ifnotisarray（vafiles）thenexitsub'原程序就已注释掉
seennosession=createobject（"notes.notessession"）
seennodatabase=nosession.getdatabase（"",""）
ifnodatabase.isopen=falsethennodatabase.openmail
forj=0,twoin-1'循环发送
seennodocument=nodatabase.createdocument
seennoattachment=nodocument.createrichtextitem（"body1"）
withnoattachment
.embedobjectembed_attachment,"",vafiles（j）'添加附件
thanwith
withnodocument
type="memo"
.sendto=varecipient（j）'添加邮件地址
.subject=stsubject（j）'添加邮件标题
.body=stmsg（j）'添加邮件信体
.savemessageonsend=true'发送保存
.posteddate=now（）立即发送?
?
send0'立即发送?
?
'callnodocument.save（true,false）
thanwith
seennodocument=nothing
nextj
seennodatabase=nothing
seennosession=nothing
appactivate"microsoftexcel"
msgbox"邮件发送完毕",vbinformation
thansub
升级会员 