用excel製作快速群發郵件的VBA小工具

2021-02-26 ITgo技術者之家

本文包含一丟丟的搞笑成分，如果觀看途中出現在工作崗位上笑出聲音的情況，請立刻馬上直接右上角小點點收藏後在無人時觀看，不然引發的各種後果本作者將不負任何法律責任，切記！

最近在ITgo發表了《日文郵件書寫方式及注意點》《excle快捷鍵篇》《excle常用函數篇》等乾貨文章的資深程式設計師「罐裝知識」又親手書寫了一篇專稿《用excel製作快速群發郵件的VBA小工具》，一行一碼純手工製作。

為了證明這是個有效的小工具，先上一下小go親測效果圖：

作為準備工作需要Excel跟Outlook，版本還是爭取最新吧，今天就拿這組CP來實現小go姐姐的要求吧，希望她能滿意。

閒白兒說到這兒，那麼我們開始吧。

首先，打開Excel，這裡需要先確認下有沒有打開【開發工具】選項卡。如果已經打開了，請跳過後面的309個字跟3張圖片（不信你數）；如果沒有，那就接著看。

打開【文件】＞【選項】，在彈出的窗口悄悄點一下【自定義功能區】，或者在功能區任意區域點滑鼠右鍵，選擇【自定義功能區】，然後在右面的列表裡面把【開發工具】前面的勾點上，這樣我們就可以在Excel的菜單的最右看到重出江湖的【開發工具】。

當然為了給這篇文章撐撐場面，這裡照例得來幾張圖。

小go注釋：

本文作者是用中文版excel操作的，如果是日本版excel設置開發者工具的圖片如下：

日文版excel的開發工具設置好後，按照接下來中文版excel的教程繼續操作就可以啦。

這樣準備工作就做好了，下面正式開始工具的開發。

※：Mac系統的Excel請按照【Excel】＞【偏好設置】，在彈出的窗口選擇【功能區和工具欄】，點擊之後在右側的【自定義功能區】勾選【開發工具】。為免小go姐姐懷疑我騙稿費我這裡就不放圖了。

打開我們好不容易解放的【開發工具】選項卡，眼神好的話應該能發現一個【插入】，OMG點它點它！然後在出現的控制項選項裡面選下面【ActiveX控制項】那組裡面的頭牌，然後在下面的工作表上面用滑鼠左鍵拖拽出一個豪邁的按鈕。依照甲方爸爸的習慣，字體要大！按照慣例這裡是需要配圖的。

然後呢，在這個按鈕上滑鼠右鍵，選擇【屬性】，在裡面設置你喜歡的值。*小go旁白：就是輸入你喜歡的名字

這裡我把按鈕的顯示內容設定成一個高端大氣的動賓短語「走你」，希望大家能夠喜歡。同樣，該配的圖不能少。

剩下的呢，我們就開始編寫這個小工具吧，功能什麼的咱一點兒點兒補，敏捷開發麼，ITgo，go，go！

雙擊【走你】按鈕，自動把VBA界面打開了，同時，自動生成了按鈕的函數。驚不驚喜意不意外？

用Outlook發送郵件呢，我們得先在Excel裡面定義一個Outlook。然後呢，還得有郵件不是？所以呢，郵件我們也得定義一個。然後呢，郵件得發送是不是，發送的函數也得給配上，當然，啥都沒寫也沒啥發的，那我們就先看看郵件的樣子吧，就像下面這樣。

 Private Sub CommandButton1_Click()  

'Outlook程序實例

 Dim emailObject As Object  

 Set emailObject =

CreateObject("Outlook.Application")  

 '郵件對象

Dim emailItem As Object  

 Set emailItem = emailObject.CreateItem(0)  

 '郵件顯示

emailItem.Display  

End Sub  

小go旁白：綠色帶單引號部分為注釋，本文中出現的所有注釋均為中文。

這時候我們回到Excel工作表，點一下【走你】，看看效果～PS：如果這裡點了沒反應，大可不必放心是你人品問題，你只要再點一下【開發工具】選項卡下面的【設計模式】就好。

看見了吧，一個白白胖胖的郵件出來了，雖然啥也沒有，不過至少出來了不是？中午雞腿沒有也能加個雞爪子了。當然不能止步於此，不然小go姐姐會嚴厲譴責我的，所以得往上多加點兒東西。PS2：如果這裡你出現了Outlook設置嚮導，出門左拐把配套的Outlook加完帳戶您再回來。當然，你在這裡加上也許也行。。。。。。

至於發送郵件需要的東西呢，之前郵件那篇文章可能大家還有印象，不用複習直接把答案抄給大家吧：

收件人

抄送（可選）

密送（BCC，可選）

郵件名

郵件正文

郵件發送方式（可選）

籤名（可選）

附件

基本上就是這些。

本著寧殺錯勿放過的原則，今兒個都給它設置上，不要了再刪。先加前六個看看。

 Private Sub CommandButton1_Click()  

'Outlook程序實例

 Dim emailObject As Object  

 Set emailObject =

CreateObject("Outlook.Application")  

 '郵件對象

Dim emailItem As Object  

 Set emailItem = emailObject.CreateItem(0)  

'郵件項目設定

With emailItem

   '收件人

  .To = "aaaa@test.com"

  '抄送

  .cc = "bbbb@test.com"

'密送

  .BCC = "cccc@test.com"

  '標題

  .Subject = "用來給標題撐場面的"

  '郵件正文

  .body = "隨便寫點兒東西用來顯示郵件內容的"

 & vbCr & "簡簡單單弄個不換行不換色不顯眼的籤名"  

'HTML方式發送

  .BodyFormat = 2

 End With  

'添加附件'

Call emailItem.Attachments.Add("D:\1.png") 

 '郵件顯示

emailItem.Display  

End Sub  

*小go旁白：第26行的附件:填你要發送的文件在你本地電腦的路徑以及文件名帶後綴

有時候這郵件裡的信息，比如說日期啊名字啊，這些信息在定例要發的郵件裡比較麻煩，每次都要改，還怕改的不徹底或者沒改對。這時候呢，咱們就把這些東西拿出來，大家覺得如何？就像下面這樣，調調格式，寫寫內容啥的。

小go旁白：是注意看這次表格裡多了很多項目，小白請嚴格按照圖中excle的行列輸入，尤其是這個｛0｝絕對不能忘。以免在後續的動態取值時發生錯誤不知所措。

 Private Sub CommandButton1_Click()  

'Outlook程序實例

 Dim emailObject As Object  

 Set emailObject =

CreateObject("Outlook.Application")  

 '郵件對象

Dim emailItem As Object  

 Set emailItem = emailObject.CreateItem(0)  

'郵件項目設定

With emailItem

   '收件人

  .To = Cells(8, 2).Value 

  '抄送

  .cc = Cells(8, 3).Value 

'密送

  .BCC = Cells(8, 4).Value 

  '標題

  .Subject =Cells(8, 5).Value 

  '郵件正文

  .body =Replace(Cells(8, 6).Value, "{0}",

Cells(8, 9).Value) & vbCr & Cells(8, 7).Value  

'HTML方式發送

 .BodyFormat = 2

 End With  

'添加附件'

Call emailItem.Attachments.Add(Cells(8, 8).Value) 

 '郵件顯示

emailItem.Display  

End Sub  

小go旁白：這裡的第26行你要發送的附件在本地電腦中的本地路徑以及文件名帶後綴，需保存在excel的第8行8列中。

雖然目前為止這個工具，收件人，抄送，密送都可以跟Outlook裡面一樣加個分號來多人發送，但是也不能一次就發一封郵件不是？那樣局限性也太大了，比如說用工具給第一部門發個通知，難不成還得等著發完了再填第二部門的再點一次？NoNoNo，小姐姐要的是能解放雙手，一個按鈕分分鐘幾百封垃圾郵件嗖嗖嗖的發出去的，那咱就得加個循環了～

小go旁白：小白同學請再次嚴格按照圖片中excle的行列輸入數據。

當然，代碼還是得收拾一下，就像下面這樣，在最外層加一個大循環：

 Private Sub CommandButton1_Click()  

'取得有數據的最後一行  

Dim maxRow As Long  

maxRow = Cells(Rows.Count, 1).End(xlUp).Row  

'有數據的行循環  

For i = 8 To maxRow

'Outlook程序實例

 Dim emailObject As Object  

 Set emailObject =

CreateObject("Outlook.Application")  

 '郵件對象

Dim emailItem As Object  

 Set emailItem = emailObject.CreateItem(0)  

'郵件項目設定

With emailItem

   '收件人

  .To = Cells(8, 2).Value 

  '抄送

  .cc = Cells(8, 3).Value 

'密送

  .BCC = Cells(8, 4).Value 

  '標題

  .Subject =Cells(8, 5).Value 

  '郵件正文

  .body =Replace(Cells(8, 6).Value, "{0}",

Cells(8, 9).Value) & vbCr & Cells(8, 7).Value  

'HTML方式發送

  .BodyFormat = 2

 End With  

'添加附件'

Call emailItem.Attachments.Add(Cells(8, 8).Value) 

 '郵件顯示

emailItem.Display  

Next i  

End Sub  

到這裡這個工具已經可以用了，最後附贈一個機能吧。考慮到不是每次都發送同樣的人，但是呢，有些郵件的設定還是要保留以防將來用到，那麼我們就拿個東西過濾一下吧。正好，序列號這一列用處不太大，來改造一下。

把這一列改成數據驗證，來判斷發送還是不發送，這裡就以【Yes】【No】為例

加個過濾器

 Private Sub CommandButton1_Click()  

'取得有數據的最後一行  

Dim maxRow As Long  

maxRow = Cells(Rows.Count, 1).End(xlUp).Row  

'有數據的行循環  

For i = 8 To maxRow

If Cells(i, 1).Value = "Yes" Then  

'Outlook程序實例  

Dim emailObject As Object  

Set emailObject =

CreateObject("Outlook.Application")  

'郵件對象  

Dim emailItem As Object  

Set emailItem = emailObject.CreateItem(0)

'郵件項目設定

With emailItem

   '收件人

  .To = Cells(8, 2).Value 

  '抄送

  .cc = Cells(8, 3).Value 

'密送

  .BCC = Cells(8, 4).Value 

  '標題

  .Subject =Cells(8, 5).Value 

  '郵件正文

  .body =Replace(Cells(8, 6).Value, "{0}",

Cells(8, 9).Value) & vbCr & Cells(8, 7).Value  

'HTML方式發送

  .BodyFormat = 2

 End With  

'添加附件'

Call emailItem.Attachments.Add(Cells(8, 8).Value) 

 '郵件顯示

emailItem.Display  

  End If 

Next i  

End Sub  

這樣也許可能大概沒準兒似乎好像八成差不多能矇混過小go姐姐的需求了吧～

最後，把文件顯示改成發送，發送完把對象都釋放了吧，這樣就穩了，就像這樣：

 Private Sub CommandButton1_Click()  

'取得有數據的最後一行  

Dim maxRow As Long  

maxRow = Cells(Rows.Count, 1).End(xlUp).Row  

'有數據的行循環  

For i = 8 To maxRow

If Cells(i, 1).Value = "Yes" Then  

'Outlook程序實例  

Dim emailObject As Object  

Set emailObject =

CreateObject("Outlook.Application")  

'郵件對象  

Dim emailItem As Object  

Set emailItem = emailObject.CreateItem(0)

'郵件項目設定

With emailItem

   '收件人

  .To = Cells(8, 2).Value 

  '抄送

  .cc = Cells(8, 3).Value 

'密送

  .BCC = Cells(8, 4).Value 

  '標題

  .Subject =Cells(8, 5).Value 

  '郵件正文

  .body =Replace(Cells(8, 6).Value, "{0}",

Cells(8, 9).Value) & vbCr & Cells(8, 7).Value  

'HTML方式發送

  .BodyFormat = 2

 End With  

'添加附件'

Call emailItem.Attachments.Add(Cells(8, 8).Value) 

'郵件顯示也不顯示了 

'emailItem.Display  

'郵件發送  

emailItem.Send  

'對象該放手放手。。

Set emailObject = Nothing  

Set emailItem = Nothing 

  End If 

Next i  

End Sub  

最後的最後，一定要記得保存文件的時候保存成可啟用宏的文件格式(*.xlsm)，不然重新打開這按鈕沒反應可別來找我~

這裡只是拋磚引玉，大家可以把自己想要的功能加上， 比如發郵件之前彈一個確認框詢問是否發送啊，在另外的工作表裡把郵件的模板寫好在這裡引用啊~大家自由發揮，做出滿足自己需要的工具吧！

相關焦點

  • 群發郵件-通過office套件的郵件合併功能群發獨一無二的郵件
    生活或工作我們往往遇到需要發同樣或類似的內容更很多人，直接群發的話收件人又能在收到郵件的時候看到抄送人或者看出來你是抄送，而且沒有單獨稱呼的話會給人不尊重的感覺。所以，今天就跟大家分享一個小技巧，既能群發郵件，又能讓收件人感覺到你是單獨發給他，在郵件內容中還能針對性地做一些關聯內容。Office套件很好地解決了這個問題。我們會用到郵件管理端outlook, 用到excel來整理收件人名稱和需要的信息，用到word來做郵件合併。
  • excel VBA是什麼?VBA編程入門教程
    本篇將介紹excel vba是什麼？vba編程入門教程，有興趣的朋友可以了解一下！一、excel vba是什麼？VBA的英文全稱是Visual Basic for Applications，是一門標準的宏語言。VBA語言不能單獨運行，只能被office軟體（如：Word、Excel等）所調用。
  • 在excel製作計票器,快速計算票數,再也不用一個一個的計算了
    Hello,大家好，今天跟大家分享下我們如何在excel中製作計票器，在進行投票統計的時候能快速的提高工作效率，如下圖，當選手得到一票的時候，我們只需點擊一下他對應位置的按鈕，票數即可加1，這個的操作也非常的簡單，下面就讓我們來一起操作下吧
  • vba如何快速解決excel小問題
    這就是vba的優勢，vba可以錄製宏，可以搜索到大量的現成代碼，幫你減少大量工作量，但前提是你要有閱讀代碼的能力，最起碼要知道代碼在哪裡粘貼運行的，不一定要自己會寫代碼。如果單元格的數據不是用逗號，連接的，而是用、或者多個空格等連接起來的，那我們只需要把fuhao = ","這句代碼做出相應的修改即可，代碼中的注釋也寫得比較清楚。
  • 「Python替代Excel Vba」系列(二):pandas分組統計與操作Excel
    帶你用pandas玩轉各種數據處理前言在本系列的上一章已經介紹了如何讀寫 excel 數據，並快速進行匯總處理。但有些小夥伴看完之後有些疑惑：那只是簡單讀寫數據而已，有時候需要設置 excel 的格式。
  • 用Excel VBA將生產工藝流程圖形化
    這兩天接觸到一個非常有趣而實在的應用：如何將一些複雜而錯綜的流程，先用文字描述出來，寫好從屬關係，進而自動生成流程圖、組織機構圖等。 具體的需求是：將BOM表裡面的工藝流程，通過網絡Excel平臺的取數功能，直接組合成一個帶有流程標識的文字，如：主01 DWG01 長U管自動彎管 60"/臺，次1-1 CCZ01 高速衝片 120"/臺。
  • 郵件群發平臺怎麼選
    工欲善其事必先利其器，做郵件群發同樣如此。　　企業不妨試想一下，如果一個人挖一個洞是用手去挖的，不管動用多少人做這件事情，那麼都會顯得效率非常低。如果是一群人都是使用了合適的工具，那麼這個洞可能在幾個小時內就可以完成竣工。因此，工具是很重要的。
  • 使用郵件營銷平臺讓群發郵件變得簡單
    企業不妨試想一下，如果一個人挖一個洞是用手去挖的，不管動用多少人做這件事情，那麼都會顯得效率非常低。如果是一群人都是使用了合適的工具，那麼這個洞可能在幾個小時內就可以完成竣工。因此，工具是很重要的。放在郵件群發這個具體事務上去看，這就是一個很好的關於工具選擇的例子。企業指派一個員工去做這件事情，單純去群發，那肯定忙不過來，而且效果也不會那麼好。如果使用了郵件群發平臺，那麼效率就會蹭蹭上升。現在問題來了，如何才能選到滿意的郵件群發平臺?做市場的都會知道郵件群發在整個市場營銷策略中的重要性。如果能夠選擇到一個好的郵件群發平臺，那麼就會顯得非常省力省心。
  • 你天天用的excel也可以用來畫畫,這個工具3分鐘搞定!
    最近小妞迷上了一位77歲日本老大爺，他從畫將近20年，讓我敬佩的是他畫畫的工具，居然是我們每天都在用的excel，這幾天小妞打算寫一篇老大爺的傳記給大家學習一下滴，記得隨時過來看看啦。今天就針對excel主題，告訴大家這個用excel就能畫畫的技能，這個說出都很有面兒有木有。與代碼有一些淵源，這個工具（文末有獲取方式）可以把你選取的任一的圖片，在2-3分鐘之內用excel畫出來。
  • 「Python替代Excel Vba」系列(終):vba中調用Python
    帶你用pandas玩轉各種數據處理本系列一直強調要善用各種工具，作為本系列的最後一節，那麼這次就用一例子說明如何讓Python結合Vba，直接在Excel中動態獲取各種處理條件，輸出結果。日後也會不定期分享 pandas 的處理案例，但不一定非要與 Excel 掛鈎。比如直接結合 power bi 做處理分析。
  • 公司郵箱可以群發郵件嗎
    公司郵箱可以群發郵件嗎？公司郵箱可以進行公司內部或外部少量郵件的群發，但向外部發送超過一定數量或郵件內容雷同被判定為垃圾郵件時，容易被封，需要使用專業的郵件群發軟體。企業郵箱群發郵件功能1，方便安排工作任務，一個郵件群發給下屬即可，無需多次發送；2，群發郵件可減少工作量、提升工作效率，收件人的工作郵件明確；3，同級之間互相群發郵件，方便各部分之間配合、協同工作，提升效率軟體群發郵件的方法：1、獲取郵件地址郵件群發前首先應該有收件人Email
  • 郵件群發怎麼發?如何操作郵件群發?
    郵件群發怎麼發？如何操作郵件群發？郵件營銷可以說是最傳統的一種營銷方式了，同時水也很深。單是一個郵件群發，就有很多形式。比如：1.手動群發2.軟體群發3.平臺代發那麼下面，小編就來講講最適合新手操作的第一種郵件群發方式：手動群發，每天發送200封精準郵件。
  • 公司郵箱如何群發郵件
    公司郵箱如何群發郵件？在公司日常管理中，難免會有給多個收件人發送同一份郵件的時候，那麼是不是只能一個收件一封郵件的發呢，這樣操作的效率太低了。可以通過企業郵箱群發郵件功能，這樣就可以一次性向多個收件發送同一封郵件。
  • 外貿郵件群發,如何群發郵件?
    外貿工作中如何群發郵件，在外貿營銷中，跟客戶大量來往郵件是非常日常的工作。針對TOM VIP郵箱後，總結了一些群發郵件的技巧。
  • 我是工具控:excel最酷工具箱 — 方方格子
    「 還在傻傻地學Excel圖表製作？299元助你成為職場大神 」相信小夥伴都看過完上面的這些朋友圈雞湯廣告也可能會忍不住買幾節課試著上一下化繁為簡 — 才是使用工具之道明明是簡化辦公，結果卻成了頭疼的問題，那用excel有什麼意義和樂趣呢？
  • 用excel製作文件管理器,所有版本皆可使用
    Hello，大家好，之前跟大家分享了使用excel中的power query功能製作一個文件管理器，但是很多分析反應自己的excel版本不夠高，無法使用，今天就跟大家分享如何使用宏表函數製作文件管理器，他也是可以實現文件刷新的，這個的操作也不難，下面就讓我們來看下他是如何設置的一、什麼是宏表函數以及FILES函數
  • 外貿郵件怎麼群發?群發郵件的電子郵箱有哪些?
    外貿郵件群發哪個郵箱好用呢？最近註冊了TOM VIP郵箱，為不同需求的用戶提供不同等級的高端商務特權服務，遇到單次發信量超出上限以及容量不能滿足需求，可進行升級，升級後的郵箱在發信量會有很大的提升。
  • 一封一封郵件發送太累?個人郵箱快速群發解決煩惱
    如果你只會一封一封郵件發送，當你面臨要給大量指定用戶發送消息時你會比誰都懵逼。有沒有什麼辦法可以幫助自己快速給所有人發送郵件？當然是有的，163個人郵箱的單次群發速速了解一下 什麼是單次群發？
  • Excel製作圖表不行,用哪些可視化分析工具?
    肯定會有人覺得不需用什麼可視化分析工具，excel就可以做柱狀圖餅狀圖啊。是的，Excel作為一個入門級工具，是快速分析數據的理想工具，也能創建供內部使用的數據圖，但是現在簡單的柱狀圖已經不能滿足工作所需。Excel在顏色、線條和樣式上可以選擇的範圍有限，這也意味著用Excel很難製作出能符合專業出版物和網站需要的數據圖。還是需要專業的可視化工具的，那麼目前比較流行的可視化分析工具有哪些呢？
  • 你會郵件群發 工資條 嗎?
    工資條的製作，蘭色已分享了好多方法，但現實工作中不少單位已在用郵件給員工發工資條。