這是POINT小數(shù)點(diǎn)的第?339?篇文章

點(diǎn)點(diǎn)寫(xiě)在前面：

之前我們有分享過(guò)一個(gè)場(chǎng)景1：你制作了一份總表你想要拆分成各個(gè)分公司，并且你需要對(duì)分公司的多個(gè)同事發(fā)送郵件。如果有20幾個(gè)分公司，你要拆分+寫(xiě)郵件+選擇接收者=20多次。

場(chǎng)景2：收集不同分公司的發(fā)過(guò)來(lái)的郵件進(jìn)行整合匯總，同樣的，如果你有20幾個(gè)分公司發(fā)過(guò)來(lái)，你需要做20幾次匯總。

power BI是能解決其中的匯總與拆分，但是很多特定的場(chǎng)景，還是需要由VBA定制解決。

所以我們經(jīng)過(guò)大家反映，重新寫(xiě)了VBA代碼，讓大家更加智能化去運(yùn)用這些場(chǎng)景。并且我們也對(duì)代碼結(jié)構(gòu)進(jìn)行了通俗易懂地講解。

這些都會(huì)更新在VBA代碼長(zhǎng)期分享文件夾里面。就讓我們先來(lái)看下視頻吧，一篇干貨哦

干貨篇幅較長(zhǎng)，先給大家看結(jié)果

具體如何完成呢？正式走進(jìn)文章一起練習(xí)啦

—?1?—VBA是什么電腦和人腦有很大的差別，有些事情人腦處理的很快，但是電腦處理的就慢，比如對(duì)圖像的識(shí)別，人一眼就能看出來(lái)一張照片上有什么內(nèi)容，可電腦對(duì)圖像的識(shí)別也只是最近幾年才剛剛能實(shí)現(xiàn)的新技術(shù)，還經(jīng)常會(huì)犯錯(cuò)；但是有些工作電腦就比人腦厲害很多，比如大量的計(jì)算，比如重復(fù)做千百萬(wàn)次同樣的操作。所以，當(dāng)我們有些工作需要機(jī)械化的重復(fù)許多次的時(shí)候，把它教給電腦來(lái)處理就事倍功半，VBA就是這樣一個(gè)能讓電腦重復(fù)工作的神器！VBA是微軟基于VB語(yǔ)言開(kāi)發(fā)的一種寄生于其他應(yīng)用程序里的語(yǔ)言，最常用的就是在OFFICE套件里比如Excel、World、PowerPoint都集成了VBA的開(kāi)發(fā)環(huán)境，還有些其他軟件也可以使用VBA，例如AutoCAD等。當(dāng)然，VBA被使用最多的還是在Excel里，它可幫助我們處理各種需要大量重復(fù)的機(jī)械化操作。—?2?—VBA和Python對(duì)比的優(yōu)點(diǎn)另外提一下，近幾年也有不少人用比較熱門的Python語(yǔ)言來(lái)處理Excel表，不可否認(rèn)，Python是一門強(qiáng)大的語(yǔ)言，處理表格也能又快又好。不過(guò)VBA在處理Excel表格上比起Python還是有很多很多的優(yōu)勢(shì)的，這里簡(jiǎn)單的說(shuō)幾條：1、例如，VBA不需要再安裝軟件部署開(kāi)發(fā)環(huán)境，只要安裝了Office的Excel軟件就能直接使用，而想用Python還需要進(jìn)行復(fù)雜的安裝設(shè)置；2、VBA與Excel結(jié)合的非常好，可以直接調(diào)用絕大多數(shù)的Excel功能，比如復(fù)制、粘貼、篩選、填充公式等；3、VBA可以直觀的看到每段代碼的運(yùn)行效果，可以一步一步的看你的原始表格是怎么被轉(zhuǎn)換運(yùn)算的，編寫(xiě)調(diào)試非常直觀；4、VBA還有個(gè)錄制宏的逆天功能，可以錄下來(lái)你的操作步驟，經(jīng)過(guò)簡(jiǎn)單的修改就能使用。—?3?—案例介紹這次，我就帶來(lái)了兩段VBA代碼的解析，如何把一張表根據(jù)某列的內(nèi)容拆分成多個(gè)文件，并且可以把這些文件分別發(fā)送E-Mail給不同的人。這段代碼適合什么做什么用呢？比如有些銷售數(shù)據(jù)需要分別發(fā)送給不同的分部，比如工資表需要分別發(fā)送給不同的部門或員工等等情形。手工操作需要進(jìn)行篩選、復(fù)制、新建一張表、另存、編寫(xiě)郵件填寫(xiě)內(nèi)容和標(biāo)題、添加附件、發(fā)送郵件。好了，終于完成了一個(gè)文件的發(fā)送，然后以上步驟再重復(fù)操作幾十次，煩死了！這種機(jī)械化的重復(fù)勞動(dòng)交給VBA來(lái)啊，幾十秒就能全部完成了！本次案例是對(duì)一個(gè)銷售的明細(xì)表按照F列的城市名進(jìn)行拆分，然后把每個(gè)文件分別發(fā)送給不同的城市經(jīng)理。

—?4?—拆分文件案例解析用VBA編程其實(shí)還挺容易的，其實(shí)編程的框架已經(jīng)出來(lái)了，就是上面我們那些手工操作的步驟，我們用計(jì)算機(jī)的代碼一步一步實(shí)現(xiàn)這些步驟，并讓電腦自己重復(fù)干幾十次就好了。那么第一個(gè)步驟是什么呢？首先，我們需要給經(jīng)常用的工作簿、工作表、單元格起個(gè)昵稱，這樣就不用每次都寫(xiě)全名了：

1.? '設(shè)定本工作表為Wb1,設(shè)定要拆分的表為Sht,設(shè)定要拆分的列的表頭單元格為Fie?? 2.? Set?Wb1?=?ThisWorkbook?? 3.? Set?St1?=?Wb1.Sheets("銷售明細(xì)表")?? 4.? Set?Fie?=?St1.Range("F1")??

然后，我們要搞清楚有哪些城市需要拆分是吧，所以先獲取一個(gè)有哪些城市的名單。獲取名單這事在VBA里最適合的就是用字典功能了。我們可以對(duì)要拆分的F列從F2開(kāi)始一直到表格最后一行，把每個(gè)單元格的值都拿出來(lái)，如果是個(gè)新內(nèi)容就加入到字典里，如果重復(fù)了就不管了

5.? '建立一個(gè)拆分關(guān)鍵字的字典?? 6.? Set?Dic?=?CreateObject("Scripting.Dictionary")?? 7.? '對(duì)表頭下的所有單元格進(jìn)行循環(huán)?? 8.? For?Each?Rng?In?St1.Range(Fie.Offset(1,?0),?Fie.End(xlDown))?? 9.? ????'判斷當(dāng)前表格的值是否在字典內(nèi),如果不在,就添加到字典內(nèi)?? 10.????If?Not?Dic.exists(Rng.Value)?Then?? 11.????????Dic.Add?Rng.Value,?""?? 12.????End?If?? 13.Next?? 14.?

VBA可以在零點(diǎn)零幾秒內(nèi)對(duì)幾千個(gè)數(shù)據(jù)進(jìn)行處理，然后我們就可以獲取到一個(gè)城市列表啦，這列表里有9個(gè)值，分別是北京、大連、上海……運(yùn)行過(guò)程中我們可以監(jiān)視一下字典，看看結(jié)果：

好了，有了這份清單，我們就可以讓VBA開(kāi)始分別干活啦，就以這個(gè)清單為準(zhǔn)運(yùn)行9次，每次生成一個(gè)城市的文件，這時(shí)候我們就可以用到For循環(huán)功能了，對(duì)字典里的每個(gè)項(xiàng)目都運(yùn)行一次生成表格的代碼，每次的城市名字就用Itm這個(gè)變量名稱呼它：

15.'針對(duì)字典內(nèi)的每個(gè)值進(jìn)行一次操作?? 16.For?Each?Itm?In?Dic.Keys??? 17.????......?? 18.Next?? 19.?

那中間這個(gè)生成不同城市文件的代碼又怎么寫(xiě)呢？真的非常簡(jiǎn)單，把我們?nèi)粘５氖止げ僮鬓D(zhuǎn)換成代碼就好啦，篩選、新建個(gè)表、復(fù)制過(guò)去、另存就搞定啦！下面代碼的附注里都寫(xiě)上每句的功能了，就不再啰嗦介紹了：

20.'對(duì)拆分依據(jù)的列進(jìn)行篩選?? 21.Fie.AutoFilter?Field:=Fie.Column,?Criteria1:=Itm?? 22.? 23.'新創(chuàng)立一個(gè)工作薄并設(shè)定為Wb2，其中第一個(gè)表設(shè)定為St2?? 24.Set?Wb2?=?Workbooks.Add?? 25.Set?St2?=?Wb2.Sheets(1)?? 26.? 27.'把當(dāng)前表格區(qū)域所有沒(méi)隱藏的單元格區(qū)域設(shè)定為Rng?? 28.Set?Rng?=?Fie.CurrentRegion.SpecialCells(xlCellTypeVisible)?? 29.? 30.'把Rng區(qū)域復(fù)制并選擇性粘貼列寬到新表(保持新舊表列寬是一樣的)?? 31.Rng.Copy?? 32.St2.Range("A1").PasteSpecial?Paste:=xlPasteColumnWidths?? 33.'把Rng區(qū)域復(fù)制粘貼到新表?? 34.Rng.Copy?St2.Range("A1")?? 35.? 36.'把新工作簿另存一下?? 37.Wb2.SaveAs?Filename:=ThisWorkbook.Path?&?"\拆分\"?&?Itm?&?".xls"?_?? 38.????,?FileFormat:=xlExcel8,?Password:="",?WriteResPassword:="",?_?? 39.????ReadOnlyRecommended:=False,?CreateBackup:=False?? 40.'關(guān)閉新工作簿?? 41.Wb2.Close?? 42.'取消原表的篩選?? 43.Fie.AutoFilter??

好啦，到這里，我們第一步就搞定了，已經(jīng)獲取這9張表啦：最后，只要對(duì)代碼進(jìn)行些簡(jiǎn)單的修改，例如可以用代碼把屏幕更新關(guān)閉，這樣運(yùn)行的時(shí)候屏幕就不用更新顯示每步的運(yùn)行結(jié)果，運(yùn)算的速度就更快了。然后我們這段拆分文件的代碼就大功告成！—?5?—發(fā)送郵件案例解析接下來(lái)，就到了分別發(fā)送郵件的環(huán)節(jié)了。到郵件這一步呢，操作涉及到的知識(shí)可能就多點(diǎn)了，不僅僅是要在Excel里操作單元格，還需要在硬盤(pán)里操作文件，然后還要發(fā)送E-mail。但其實(shí)只要把流程理順，知道每一步要干什么，然后一個(gè)知識(shí)點(diǎn)一個(gè)知識(shí)點(diǎn)的理順，就能完成啦！1、首先，我們?cè)O(shè)定一下郵箱聯(lián)系人的工作表以及用來(lái)存儲(chǔ)發(fā)送結(jié)果的工作表，然后找到發(fā)送結(jié)果表還沒(méi)被使用過(guò)的新列，用來(lái)存儲(chǔ)本次發(fā)送的結(jié)果

44.Set?St1?=?ThisWorkbook.Sheets("城市經(jīng)理郵箱")???? 45.Set?St2?=?ThisWorkbook.Sheets("發(fā)送結(jié)果")???? 46.???? 47.'獲取發(fā)送結(jié)果表沒(méi)使用過(guò)的列???? 48.If?St2.Range("A1").Value?=?""?Then???? 49.????Col1?=?1???? 50.Else???? 51.????Col1?=?St2.Range("XFD1").End(xlToLeft).Column?+?1???? 52.End?If???

然后再對(duì)這個(gè)列進(jìn)行簡(jiǎn)單的修飾，并填寫(xiě)列標(biāo)題

53.'填寫(xiě)發(fā)送結(jié)果列的表頭，設(shè)置列寬和居中???? 54.St2.Columns(Col1).ColumnWidth?=?25???? 55.St2.Cells(1,?Col1).HorizontalAlignment?=?xlCenter???? 56.St2.Cells(1,?Col1).Value?=?Date?&?Chr(10)?&?Time?&?Chr(10)?&?"?發(fā)送結(jié)果"????

再接著，我們需要一個(gè)變量，來(lái)記錄運(yùn)行到第幾個(gè)文件了，這樣我們就知道下次該把發(fā)送結(jié)果寫(xiě)到第幾行了。

57.n?=?0?

2、我們的初步準(zhǔn)備工作剛才已經(jīng)做完了，第二步呢，就是指定一個(gè)文件夾，然后找出其中所有的Excel文件。這一步聽(tīng)上去稍微有點(diǎn)復(fù)雜，不過(guò)其實(shí)是有套路可以用的，比如我們可以利用Windows系統(tǒng)自帶的文件ActiveX控件來(lái)實(shí)現(xiàn)這個(gè)功能：

58.'套路：獲取一個(gè)文件夾下的所有文件對(duì)象???????? 59.Dim?FS,?F,?FF,?Fil???????? 60.Set?FS?=?CreateObject("Scripting.FileSystemObject")???????? 61.Set?F?=?FS.GetFolder(文件夾路徑)???????? 62.Set?FF?=?F.Files???????? 63.For?Each?Fil?In?FF???????? 64.????......???????? 65.Next?

在上面這段代碼里，FS就是控件，F就是獲取到了一個(gè)文件夾對(duì)象，FF就是文件夾里的所有文件，然后我們?cè)儆肍or循環(huán)對(duì)FF里的每一個(gè)文件進(jìn)行操作，這樣我們就能獲取到每個(gè)文件啦。

3、獲取到文件之后再做什么呢，當(dāng)時(shí)是找一下這個(gè)文件對(duì)應(yīng)的收件人和郵箱啦，那怎么找呢？還記得文章最上面說(shuō)的“VBA與Excel結(jié)合的非常好”這句特性嗎，在這里我們可以很簡(jiǎn)單的利用Excel里的Match函數(shù)來(lái)搞定：

66.在通訊表的A列里用Excel的Match公式查找文件名所在行號(hào)?? 67.Row1?=?Application.Match(FN,?St1.Range("A:A"),?0)??

進(jìn)行查找之后，就該對(duì)結(jié)果進(jìn)行一些分析，看看查找的結(jié)果是否滿足發(fā)送郵件的要求，比如有可能找不到收件人，也有可能收件人的郵箱沒(méi)填寫(xiě)或者格式不對(duì)，所以要進(jìn)行一些判斷

68.'用來(lái)記錄操作到第幾個(gè)文件了?? 69.n?=?n?+?1?? 70.'獲取文件的主文件名(不包含.xlsx等拓展名的文件名)?? 71.FN?=?FS.GetBaseName(Fil)?? 72.'在通訊表的A列里用Excel的Match公式查找文件名所在行號(hào)?? 73.Row1?=?Application.Match(FN,?St1.Range("A:A"),?0)?? 74.'判斷下如果Row1是個(gè)錯(cuò)誤值，就是沒(méi)找到聯(lián)系人?? 75.If?IsError(Row1)?Then?? 76.????St2.Cells(n?+?1,?Col1).Value?=?"找不到“"?&?FN?&?"”的聯(lián)系人"?? 77.'判斷一下找到的聯(lián)系人的郵箱是否是*@*.*格式?? 78.ElseIf?Not?St1.Cells(Row1,?3).Value?Like?"?*@?*.?*"?Then?? 79.????St2.Cells(n?+?1,?Col1).Value?=?"“"?&?FN?&?"”郵箱地址不正確"?? 80.'如果找到聯(lián)系人，又獲取到了郵件地址，就開(kāi)始發(fā)送郵件?? 81.Else??

4、現(xiàn)在，已經(jīng)做完所有的準(zhǔn)備工作啦，要開(kāi)始發(fā)郵件啦，激動(dòng)嗎？不過(guò)，發(fā)送郵件之前呢，還需要獲取到一些郵箱的設(shè)置信息，文末附錄里會(huì)以QQ郵箱為例講解怎么開(kāi)通用第三方工具發(fā)送郵件的方法。其他郵箱可以在網(wǎng)上搜一下該如何設(shè)置，企業(yè)郵箱問(wèn)IT小哥哥要一下參數(shù)就好啦！發(fā)送郵件需要用到一個(gè)CDO.Message的控件，先聲明一個(gè)控件變量：

82.Set?Cm?=?CreateObject("CDO.Message")????'創(chuàng)建郵件對(duì)象??

然后，這個(gè)控件主要分為幾部分：(1)設(shè)置郵件的發(fā)件人郵箱、收件人郵箱、主題、正文、附件等內(nèi)容(注，在我這個(gè)案例里，我用了一個(gè)窗口來(lái)顯示和記錄郵箱的一些設(shè)置信息，這樣可以比較方便的更換其他郵箱發(fā)送，代碼里的“UserForm1.TextBox*”就是這些郵箱信息)：

83.'設(shè)置發(fā)信人的郵箱、收件人郵箱、郵件主題、郵件正文?? 84.Cm.From?=?UserForm1.TextBox1?? 85.Cm.To?=?St1.Cells(Row1,?3).Value?? 86.Cm.Subject?=?FN?&?"銷售表"?? 87.Cm.TextBody?=?"親愛(ài)的領(lǐng)導(dǎo)和同事："?_?? 88.&?Chr(10)?&?"????????附件為?"?&?FN?&?"?昨日的銷售情況表，請(qǐng)您查收。"?&?Chr(10)?&?"謝謝！"?? 89.'郵件添加附件?? 90.Cm.AddAttachment?Pth?&?Fil.Name??

(2)設(shè)置發(fā)件服務(wù)器信息

91.'對(duì)發(fā)件服務(wù)器進(jìn)行配置?? 92.stUl?=?"http://schemas.microsoft.com/cdo/configuration/"?? 93.With?Cm.Configuration.Fields?? 94.????.Item(stUl?&?"smtpusessl")?=?1?? 95.????.Item(stUl?&?"sendusing")?=?2?? 96.????.Item(stUl?&?"smtpserver")?=?UserForm1.TextBox3?'SMTP郵件服務(wù)器地址?? 97.????.Item(stUl?&?"smtpserverport")?=?UserForm1.TextBox4?'SMTP郵件服務(wù)器端口?? 98.????.Item(stUl?&?"smtpauthenticate")?=?1?? 99.????.Item(stUl?&?"sendusername")?=?UserForm1.TextBox1?'發(fā)件人郵箱?? 100.???.Item(stUl?&?"sendpassword")?=?UserForm1.TextBox2?'發(fā)件人密碼/授權(quán)碼???? 101.???.Update?? 102.End?With??

(3)?? 發(fā)送郵件這部就相當(dāng)簡(jiǎn)單啦：

103.'已完成設(shè)置，開(kāi)始發(fā)送郵件?? 104.Cm.Send??

(4)發(fā)送之后，可以獲取到是否成功發(fā)送的反饋結(jié)果，寫(xiě)入結(jié)果表：

105.'發(fā)送后生成反饋信息?? 106.If?Err.Number?=?0?Then?? 107.????St2.Cells(n?+?1,?Col1).Value?=?"“"?&?FN?&?"”發(fā)送成功"?? 108.Else?? 109.????St2.Cells(n?+?1,?Col1).Value?=?"“"?&?FN?&?"”發(fā)送失敗"?? 110.????Err.Clear?? 111.End?If??

?再對(duì)代碼進(jìn)行些簡(jiǎn)單的修飾就可以啦！比如在代碼的最后，我們可以加上這么一句來(lái)通知我們發(fā)送完畢啦：

112.msgbox("發(fā)送完畢")??

下面就是本地案例的執(zhí)行情況：

我的郵箱里也收到了一堆測(cè)試和演示時(shí)收到的郵件呢：

另外，成都的那個(gè)郵箱是我隨便敲的，所以不一會(huì)兒就收到了退信，但是這個(gè)就不是VBA代碼所關(guān)注的范疇啦，因?yàn)樗?dāng)時(shí)真的發(fā)送成功了。

—?6?—尾聲好了，我們的案例講解到此就結(jié)束啦，講解的內(nèi)容稍微有些長(zhǎng)，不過(guò)當(dāng)你把內(nèi)容全都設(shè)置好，下次工作起來(lái)就很省心啦，運(yùn)行一下“拆分”，再運(yùn)行一下“發(fā)送”，等個(gè)幾十秒，工作就完成啦！附：如何開(kāi)通QQ郵箱的第三方發(fā)件功能現(xiàn)在的免費(fèi)郵箱為了安全起見(jiàn)，大部分都默認(rèn)關(guān)閉了第三方發(fā)送功能，所以需要手工開(kāi)通。進(jìn)入到QQ郵箱的“設(shè)置”功能，打開(kāi)“賬戶”的設(shè)置選項(xiàng)，找到下面的“POP3/IMAP……服務(wù)”這一項(xiàng)，把對(duì)應(yīng)的“POP3/SMTP服務(wù)”開(kāi)啟。

然后跟著提示，可能需要編寫(xiě)個(gè)短信發(fā)給騰訊，然后就能開(kāi)通第三方發(fā)件功能啦。成功開(kāi)通服務(wù)之后，QQ郵箱會(huì)顯示一個(gè)授權(quán)碼，要把這個(gè)授權(quán)碼保存一下，在發(fā)信的時(shí)候需要需要用到。這個(gè)授權(quán)碼是做什么呢的，其實(shí)它就是一個(gè)只能用來(lái)發(fā)郵件的密碼，不能登錄郵箱，也不能看到郵件內(nèi)容。在早些年，用第三方軟件收發(fā)信件要把郵箱密碼保存到OutLook或者FoxMial軟件里的，像我們的VBA代碼也需要用明文存儲(chǔ)這個(gè)密碼，非常危險(xiǎn)。所以騰訊有了這么個(gè)功能，保護(hù)你登錄郵箱的密碼，只能發(fā)信，這樣就算這個(gè)密碼被泄露了也沒(méi)什么損失，非常安全。當(dāng)然，這個(gè)授權(quán)碼要是泄露也不太好呢，因?yàn)閯e人就可以用你的郵箱給其他人發(fā)垃圾或者詐騙郵件，所以還是盡量別告訴別人啦，所以我的案例里也把這個(gè)授權(quán)碼刪掉了，大家想使用請(qǐng)用自己的郵箱測(cè)試哦。開(kāi)通服務(wù)之后，還需要獲取到發(fā)件服務(wù)器的地址和端口號(hào)，這個(gè)郵箱一般都有介紹，比如QQ郵箱就是點(diǎn)這里：

點(diǎn)開(kāi)文章里就會(huì)介紹相關(guān)的內(nèi)容，比如這里，我們就能獲取到發(fā)件服務(wù)器地址和端口號(hào)，分別是smtp.qq.com和465，有了這兩個(gè)信息，我們就可以用VBA發(fā)送郵件啦！

求資源，迫不及待想練習(xí)了..關(guān)注微信公眾號(hào)：POINT小數(shù)點(diǎn)數(shù)據(jù)后臺(tái)輸入關(guān)鍵字：VBA長(zhǎng)期分享你就會(huì)獲得練習(xí)所需表格《拆分郵寄案例.xlsm》，還有一些比較通用型的VBA代碼工作簿，比如拆分表格、分發(fā)郵件、合并各分公司數(shù)據(jù)等，注釋也幫你寫(xiě)好了。小伙伴們，轉(zhuǎn)發(fā)這個(gè)利器給你的小伙伴們吧，因?yàn)槲覀儠?huì)不斷更新腳本噠~大家要是遇到一些比較典型的Excel問(wèn)題需要VBA解決，可以留言，我們老師會(huì)定期篩選出典型案例，分享腳本給大家(這是免費(fèi)的哦)

大家在后臺(tái)留言不知道怎么啟用宏，點(diǎn)點(diǎn)給大家錄了視頻了哦~

同時(shí)很歡迎大家報(bào)名學(xué)習(xí)VBA課程可以讓你在實(shí)際處理Excel中大大提升工作效率！POINT.小數(shù)點(diǎn)Excel-VBA(第十期)正式預(yù)售點(diǎn)擊圖片加入看完覺(jué)得VBA太實(shí)用了！call王老師~

總結(jié)

以上是生活随笔為你收集整理的vb表格控件_（超级干货）ExcelVBA拆分表格并分别发送邮件增强版的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。