辦公自動化之-通過Excel自動傳送電子郵件

來源：酷知科普網 1.4W

工作中經常碰到一邊要在Excel中記錄日常事項，一邊又得重複的把這些事項通過郵件通知給合作客戶，其他部門，供應商等。而且郵件所需格式與Excel記錄格式又不一致，這樣就得每天反覆手工輸入拷貝相關內容。煩，煩，煩!無聊，無聊，無聊！
怎麼辦呢，其實Excel是可以有特技的！Duang,Duang,Duang, 特技一鍵搞定你的煩惱。
讓我們以常用的出貨通知為例來感受非同一般的EXCEL郵件一鍵傳送特技吧！

操作方法

（01）建立一個名字為“出貨清單”Excel表單，先製作一個出貨記錄表格。您可按需要自行製作，做成一行一條目。在正常內容最後加一行用於選擇是否需要自動傳送郵件。

（02）再增加一個名字為"清單"的Excel表單。用於列舉各項常用重複內容。例子中列舉出貨地址清單，聯絡人聯絡方式清單，還有料號清單。

（03）對各個清單定義範圍。這裡以PNlist 命名來定義舉一例，各位可按需定義。料號清單範圍定義 =清單!$G$2:OFFSET(清單!$G$1,COUNTA(清單!$G:$G)-1,0)COUNTA(清單!$G:$G) 是用於計算G列有多少行有內容，即有多少個P/N清單。例子計算結果為4OFFSET($G$1,4-1，0）計算結果即為$G$4.所以PNlist 就被成功定義為=清單!$G$2:$G$4定義地址清單：Addresslist =清單!$A$2:offset($A$1,counta($A:$A)-1,1)定義聯絡人清單：Namelist =清單!$D$2:OFFSET(清單!$D$1,COUNTA(清單!$D:$D)-1,1)

（04）通過定義的清單來校驗資料，從而保證誤輸入。通過下來選擇也可提高效率。

（05）新建一個名為“模板”的Excel表單，定義要通過郵件傳送的內容的模板。後續會通過巨集來拷貝模板，填充內容，呼叫outlook傳送。注意。模板請放在第一行以下，因為第一行會用與拷貝傳送內容過來做轉制。

（06）按如下圖片步驟錄製一個名為"shipment"的巨集。巨集的錄製是錄製單條操作的內容，操作內容根據自己需要按步驟錄製。多條迴圈操作需稍微加幾句程式碼。下一步驟會介紹。如下程式碼供參考：Sub shipment()' shipment arrangement'如下為錄製內容Sheets("出貨記錄")ctRange("B3:I3")opyMode = Sheets("郵件模板")ctRange("A1")eSpecial Paste:=xlPasteValues, Operation:=xlNone,SkipBlanks _:=False, Transpose:=FalseRange("G3:H12")opyMode = Range("A3")rt Shift:=xlDownRange("B3")opyMode = ulaR1C1 = "=R[-2]C[-1]"Range("B4")ulaR1C1 = "=R[-3]C[1]"Range("B5")ulaR1C1 = "=R[-4]C[2]"Range("B6")ulaR1C1 = "=R[-5]C"Range("B7")ulaR1C1 = "=R[-6]C[4]"Range("B8")ulaR1C1 = "=R[-7]C[5]"Range("B9")ulaR1C1 = "=R[-8]C[3]"Range("B10")ulaR1C1 = "=R[-9]C[6]"Range("B3:B10")eSpecial Paste:=xlPasteValues, Operation:=xlNone,SkipBlanks _:=False, Transpose:=FalseRange("A1:H1")opyMode = rContentsSheets("出貨記錄")ctRange("J3")ulaR1C1 = "Closed"Range("A3:J3")ctRange("J3")vateWith ern = ernColorIndex = eColor = AndShade = ernTintAndShade = 0End With'如上為錄製內容End Sub

（07）開啟錄製的巨集新增迴圈程式碼。按圖片步驟及語句在錄製範圍前後新增迴圈程式碼。Dim i As IntegerDim j As IntegerDim g As enUpdating = FalseSheets("出貨記錄")cti = 1j = tA(Range("A:A")) + 1g = 0'變數i 用於迴圈，變數j用於判斷有多少行需要迴圈，變數g 用於郵件傳送時定義有多少行需要傳送For i = 1 To jIf Range("j" & i)e = "Y" Then'如下為錄製內容-------------'如上為錄製內容g = g + 1ElseEnd IfNext i

（08）錄製範圍部分程式碼需按圖片更新成變數。

（09）再新增郵件傳送程式碼，其中有定義一個名為的 RangetoHTML（）的函式。' 以下語段用於傳送郵件Sheets("出貨記錄")ctIf g = "0" ThenMsgBox "No new shippment set to Y "Elseg = 10 * g + 2Dim OutApp As ObjectDim OutMail As ObjectDim MailBody As RangeSheets("郵件模板")ctSet MailBody = Range("A3:B" & g)Set OutApp = CreateObject("ication")Set OutMail = teItem(olMailItem)On Error Resume NextWith = " = "" = ""ect = "Shipment Arrangement"Format = Body =(MailBody)layEnd WithOn Error GoTo 0End IfSheets("出貨記錄")enUpdating = True

（10）RangetoHTML（）的函式程式碼申明將如下程式碼拷貝粘帖到End Sub()之後Public Function RangetoHTML(rng As Range)Dim fso As ObjectDim ts As ObjectDim TempFile As StringDim TempWB As WorkbookTempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ""Set TempWB = (1)With ts(1)s(1)eSpecial Paste:=s(1)eSpecial xlPasteValues, , False, s(1)eSpecial xlPasteFormats, , False, s(1)opyMode = FalseOn Error Resume ble = teOn Error GoTo 0End WithWith ( _SourceType:=xlSourceRange, _Filename:=TempFile, _Sheet:=ts(1), _Source:=ts(1)ess, _HtmlType:=xlHtmlStatic)ish (True)End WithSet fso = CreateObject("SystemObject")Set ts = ile(TempFile)AsTextStream(1, -2)RangetoHTML = eRangetoHTML = Replace(RangetoHTML, "align=centerx:publishsource=", _"align=left x:publishsource=")e savechanges:=FalseKill TempFileSet ts = NothingSet fso = NothingSet TempWB = NothingEnd Function

（11）程式碼完成。只需建立一個按鈕方便呼叫此巨集即可。

（12）增加條目後把對應行內郵件通知列改成"Y",然後點“傳送郵件”按鈕即可彈出郵件並出貨通知表單內更改狀態。

特別提示

最好是有一定的VBA基礎，沒有就仔細看裡面的公式就能明白什麼意思。

如果覺得本給您帶來一點幫助或啟發，請點頁面右上角大拇指及五角星圖示。感謝感謝！

Excel 辦公自動化電子郵件傳送自動