邮件群发不同邮件发不同人从excel利用notesmass mailingdiffere.docx
- 文档编号:6062794
- 上传时间:2023-05-09
- 格式:DOCX
- 页数:8
- 大小:16.50KB
邮件群发不同邮件发不同人从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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 邮件群发不同邮件发不同人从excel利用notesmass mailing differe 邮件 群发 不同 同人 excel 利用 notesmass