send emailtramail2文档格式.docx

send emailtramail2文档格式.docx

《send emailtramail2文档格式.docx》由会员分享，可在线阅读，更多相关《send emailtramail2文档格式.docx（12页珍藏版）》请在冰豆网上搜索。

H"

AB:

AB"

AC:

AC"

AG:

AG"

K:

K"

L:

L"

B:

U:

V"

O:

CC"

Range（"

F1"

）="

PONumber"

G1"

Store"

H1"

Line"

I1"

UPC"

J1"

VendorSKU"

K1"

Description"

L1"

QtyOrdered"

M1"

QtyReceived"

N1"

QtyOn-Order"

O1"

Comments"

P1"

EstimatedShipDate"

Q1"

EstimatedArrivalDate"

R1"

Carrier"

S1"

TrackingNumber"

T1"

ShipFromZip"

）.InsertShift:

I2:

&

[D65536].End（xlUp）.Row）.Formula="

=F2&

H2"

Calculate

SQLresults"

D2:

[B65536].End（xlUp）.Row）.Formula="

=B2&

C2"

ActiveSheet.Range（"

A1:

Z65536"

）.AutoFilterField:

=7,Criteria1:

="

C"

Operator:

=xlAnd

Rows（"

2:

2"

Range（Selection,Selection.End（xlDown））.Select

Selection.EntireRow.Delete

ActiveSheet.ShowAllData

=8,Criteria1:

Y"

=24,Criteria1:

=*ETA*"

=xlOr,Criteria2:

=*DC*"

=*RDC*"

=*CXL*"

=*ASN#*"

J2:

[E65536].End（xlUp）.Row）.Formula="

=VLOOKUP（RC[-1],'

SQLresults'

!

C[-6],1,0）"

=10,Criteria1:

#N/A"

）.Delete

Workbooks.OpenFilename:

=_

"

\\cnnts800\SS\BPO\BPO\POTracking\Contactlist\VendorContact.xls"

Allcontactlist（Newformat）"

）.Activate

E"

E2"

E2:

=RC[-4]&

RC[-1]"

Workbooks（"

"

Format（Date,"

mmddyy"

）&

_CHINA_OO_DETAILS--2ndemail.xls"

F2"

F2:

=RC[-5]&

G2"

G2:

[VendorContact.xls]Allcontactlist（Newformat）'

C5:

C7,3,0）"

Selection.Copy

Selection.PasteSpecialPaste:

=xlPasteValues,Operation:

=xlNone,SkipBlanks_

:

=False,Transpose:

=False

1:

1"

Worksheets.Add.Name="

can'

tfindvendor"

A1"

ActiveSheet.Paste

d1"

）.Value="

EmailAdress"

VendorContact.xls"

）.Close

MyNote="

Pleasemakesureyouhaveopenedthewmc-trackingemailaccount"

Answer=MsgBox（MyNote,vbYesNo）

IfAnswer=vbNoThenExitSub

rng1=Sheets（"

）.Range（Cells（1,1）,Cells（1,15）.End（xlDown））

Fori=1ToUBound（rng1）

d（rng1（i,6））="

Next

k=d.keys

Forj=1Tod.Count-1

Selection.Range（"

Selection.AutoFilterField:

=6,Criteria1:

=k（j）

y=[E65536].End（xlUp）.Row

Range（Cells（1,1）,Cells（y,21））.Select

Setnam=Workbooks.Add

h=Worksheets（"

）.Range（"

f2"

Vnam=Worksheets（"

e2"

adr=Worksheets（"

d2"

cat=Worksheets（"

c2"

dpt=Worksheets（"

b2"

Sdpt=Worksheets（"

a2"

ActiveWorkbook.SaveAs"

C:

\Emailsend\"

h&

.xlsx"

ActiveWindow.Close

Sheets.Add.Name="

adv"

G1:

G65536"

）.AdvancedFilterAction:

=xlFilterInPlace,Unique:

=True

PO#"

DimoutappAsObject

DimoutmailAsObject

DimbodyAsString

DimfnameAsString

DimStrBodyAsString

DimrngAsRange

DimpAsInteger

WithApplication

.EnableEvents=False

.ScreenUpdating=False

EndWith

Setrng=Nothing

OnErrorResumeNext

p=[a65536].End（xlUp）.Row

Setrng=Sheets（"

）.Range（Cells（2,1）,Cells（p,1））

OnErrorGoTo0

IfrngIsNothingThen

MsgBox"

Theselectionisnotarangeorthesheetisprotected"

_

vbNewLine&

pleasecorrectandtryagain."

vbOKOnly

ExitSub

EndIf

Setoutapp=CreateObject（"

Outlook.Application"

Setoutmail=outapp.CreateItem（0）

fname="

Withoutmail

.From="

wmc-tracking@"

.To=adr

IfSdpt="

BABY"

Then

Ifdpt="

BABYCARE"

Andcat="

BABYCONSUMABLES"

.cc="

slo@;

wyamasaki@"

Else

ckrause@;

m0alove@"

CONSUMABLES"

HARDLINES"

dsharma@;

elouie@"

HOME"

jmacalino@;

jnguyen@"

ELECTRONICS"

m0alove@;

xhu@"

3RD-ELECTRONICS"

xhu@;

dsaito@"

APPAREL"

tyu@;

JEWELRY"

TOYSANDSEASONAL"

SEASONALANDCELEBRATIONS"

TJames@"

MEDIAANDWIRELESS"

WIRELESS"

.Subject="

RequestTrackingInformation"

Vnam&

StrBody="

<

fontFACE=TimesNewRoman>

Hello,<

/font>

br>

fontFACE=TimesNewRoman>

Thebelowpurchaseorder（s）havereachedtheirshipdate.Pleaseconfirmiftheseordershaveshippedcomplete,andifso,pleaseprovidefulltrackinginformation,includingshipdate,originzipcode,carrierandtrackingnumber.Ifthepurchaseorder（s）havenotshipped,pleaseprovideanETAonthenewrequestedshipdate.Ifthepurchaseordershavebeenpartiallyshipped,pleaseprovidequantityshippedandbackorderdatefortheremainingquantity.<

strong>

POsrequestedfortrackinginformation:

/strong>

<

StrBody2="

fontFACE=TimesNewRoman>

PleasealsonotethatwedonotcurrentlyhavevisibilitytoASNinformationsentviaEDIandthereforewillneedtrackingconfirmationviaemail.Pleasecontact<

ahref='

mailto:

dotcomtracking@'

>

dotcomtracking@<

/a>

ifyouhaveanyquestions.<

Thankyou.<

wmctracking<

.HTMLBody=StrBody&

RangetoHTML（rng）&

StrBody2

.Attachments.Addfname

.Display

.send

Setoutmail=Nothing

Setoutapp=Nothing

Application.ScreenUpdating=True

Nextj

ActiveSheet.ShowAllData

Columns（"

DimqAsInteger

q=2

DoWhileRange（"

q）.Value<

Range（"

q）.Value=Format（Date,"

mm/dd/yy"

-EmailedVendor-rxu"

q=q+1

Loop

ActiveWorkbook.SaveAs"

\Mail\"

_CHINA_OO_DETAILS--2ndemail_Comments.xls"

EndSub

FunctionRangetoHTML（rngAsRange）

DimfsoAsObject

DimtsAsObject

DimTempFileAsString

DimTempWBAsWorkbook

TempFile=Environ$（"

temp"

/"

Format（Now,"

dd-mm-yyh-mm-ss"

.htm"

rng.Copy

SetTempWB=Workbooks.Add

（1）

WithTempWB.Sheets（"

.Cells

（1）.Past