การส่งอีเมลจาก Microsoft Excel ต้องการเพียงสคริปต์ง่ายๆ เพียงไม่กี่บรรทัด เพิ่มฟังก์ชันนี้เข้าไปในสเปรดชีท และคุณจะเพิ่มความสามารถของ Excel ได้อย่างมาก
เราเคยครอบคลุมมาโคร Excel เยอะมาก ซึ่งทำสิ่งเดียวกับสคริปต์ VBA ได้ แต่ไม่ต้องมีความรู้เขียนโปรแกรม แต่มีสิ่งขั้นสูงหลายอย่างที่ทำได้เฉพาะกับ VBA เช่น การสร้างรายงานสเปรดชีตพร้อมข้อมูล PC ทั้งหมด
ชอบดูเป็นวิดีโอดีกว่า? เรามีให้ดู!
ทำไมต้องส่งอีเมลจาก Excel?
มีหลายเหตุผลที่คุณอาจต้องการส่งอีเมลจากภายใน Microsoft Excel
อาจเป็นว่าคุณมีพนักงานอัปเดตเอกสารหรือสเปรดชีตรายสัปดาห์ และต้องการรับการแจ้งเตือนทางอีเมลเมื่ออัปเดตเสร็จ หรือคุณอาจมีสเปรดชีตผู้ติดต่อ และต้องการส่งอีเมลเดียวไปยังทุกคนพร้อมกัน
คุณอาจคิดว่าการเขียนสคริปต์ส่งอีเมลจำนวนมากจาก Excel จะซับซ้อน จริงๆ แล้วไม่ได้ซับซ้อนเลย
เทคนิคในบทความนี้ใช้ฟีเจอร์ที่มีอยู่ใน Excel VBA มานานแล้ว คือ Collaboration Data Objects (CDO)
CDO เป็นคอมโพเนนต์การส่งข้อความที่ใช้ใน Windows ตั้งแต่รุ่นแรกๆ เดิมชื่อ CDONTS แล้วเมื่อ Windows 2000 และ XP ออกมา ถูกแทนที่ด้วย "CDO for Windows 2000" คอมโพเนนต์นี้รวมอยู่ในการติดตั้ง VBA ของ Microsoft Word หรือ Excel แล้ว พร้อมใช้งาน
การใช้คอมโพเนนต์นี้ทำให้การส่งอีเมลจากผลิตภัณฑ์ Windows ด้วย VBA ง่ายมาก ในตัวอย่างนี้ คุณจะใช้คอมโพเนนต์ CDO ใน Excel เพื่อส่งอีเมลที่ส่งผลลัพธ์จากเซลล์ Excel เฉพาะเจาะจง
ขั้นตอนที่ 1: สร้างมาโคร VBA
ขั้นแรก ไปที่แท็บ Developer ของ Excel
ภายในแท็บ Developer คลิก Insert ในกล่อง Controls แล้วเลือกปุ่มคำสั่ง (Command Button)
วาดปุ่มลงในชีต แล้วสร้างมาโครใหม่โดยคลิก Macros ในริบบอน Developer
เมื่อคลิกปุ่ม Create จะเปิด VBA Editor
เพิ่มการอ้างอิงไลบรารี CDO โดยไปที่ Tools > References ใน Editor
เลื่อนลงหา Microsoft CDO for Windows 2000 Library ติ๊ก chọn แล้วคลิก OK
เมื่อคลิก OK ให้จดชื่อฟังก์ชันที่คุณวางสคริปต์ไว้ จะต้องใช้ภายหลัง
ขั้นตอนที่ 2: ตั้งค่าฟิลด์ "From" และ "To" ของ CDO
ต้องสร้างออบเจ็กต์เมลและตั้งค่าฟิลด์ทั้งหมดที่จำเป็นสำหรับการส่งอีเมล
จำไว้ว่า แม้ฟิลด์หลายตัวจะเป็นตัวเลือก แต่ฟิลด์ From และ To จำเป็นต้องมี
Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
strSubject = "Results from Excel Spreadsheet"
strFrom = "rdube02@gmail.com"
strTo = "rdube02@gmail.com"
strCc = ""
strBcc = ""
strBody = "The total results for this quarter are: " & Str(Sheet1.Cells(2, 1))จุดเด่นคือ คุณสามารถสร้างสตริงใดก็ได้เพื่อปรับแต่งข้อความอีเมลเต็มรูปแบบ และกำหนดให้ตัวแปร strBody
ต่อส่วนประกอบข้อความด้วยตัวดำเนินการ & เพื่อแทรกข้อมูลจากชีต Excel ใดๆ เข้าไปในข้อความอีเมล ได้เหมือนตัวอย่างด้านบน
ขั้นตอนที่ 3: ตั้งค่า CDO ใช้ SMTP ภายนอก
ส่วนโค้ดต่อไป คือส่วนที่ตั้งค่า CDO ให้ใช้ SMTP server ภายนอกเพื่อส่งอีเมล
ตัวอย่างนี้เป็นการตั้งค่าไม่ใช้ SSL ผ่าน Gmail CDO รองรับ SSL แต่อยู่นอกขอบเขตบทความ หากต้องการใช้ SSL โค้ดขั้นสูงใน GitHub ช่วยได้
Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields
With SMTP_Config
.Item("https://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("https://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("https://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Item("https://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com"
.Item("https://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("https://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
.Item("https://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Update
End With
With CDO_Mail
Set .Configuration = CDO_Config
End Withขั้นตอนที่ 4: เสร็จสิ้นการตั้งค่า CDO
ตอนนี้คุณตั้งค่าการเชื่อมต่อ SMTP server แล้ว สิ่งที่เหลือคือกรอกฟิลด์ที่เหมาะสมสำหรับ CDO_Mail object และสั่ง Send
วิธีทำดังนี้:
CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send
Error_Handling:
If Err.Description <> "" Then MsgBox Err.Descriptionไม่จะมีกล่องป๊อปอัปหรือข้อความแจ้งเตือนความปลอดภัย ซึ่งอาจเกิดขึ้นหากใช้ Outlook Mail Object
CDO เพียงประกอบอีเมลและใช้รายละเอียดการเชื่อมต่อ SMTP server เพื่อส่งข้อความออกไป เป็นวิธีที่ง่ายที่สุดในการรวมอีเมลเข้าในสคริปต์ VBA ของ Microsoft Word หรือ Excel
เพื่อเชื่อมปุ่มคำสั่งกับสคริปต์นี้ ไปที่ Code Editor คลิก Sheet1 เพื่อดูโค้ด VBA ของชีตนั้น
พิมพ์ชื่อฟังก์ชันที่คุณวางสคริปต์ข้างต้นไว้
นี่คือข้อความที่ผมได้รับใน Inbox:
หมายเหตุ: หากได้ข้อผิดพลาด The transport failed to connect to the server ให้ตรวจสอบว่าใส่ username, password, SMTP server และ port number ถูกต้องในบรรทัดภายใต้ With SMTP_Config
ไปไกลกว่านั้น: ทำให้กระบวนการทั้งหมดอัตโนมัติ
การส่งอีเมลจาก Excel ด้วยการกดปุ่มคือเรื่องดี แต่หากต้องการใช้งานเป็นประจำ ควรทำให้อัตโนมัติ
ต้องแก้ไขมาโคร ไปที่ Visual Basic Editor คัดลอกและวางโค้ดทั้งหมดที่เราสร้างไว้
เลือก ThisWorkbook จากลำดับชั้น Project
จากสองช่อง dropdown ที่ด้านบนของหน้าต่างโค้ด เลือก Workbook และเลือก Open จาก Methods dropdown
วางสคริปต์อีเมลข้างต้นลงใน Private Sub Workbook_Open()
สิ่งนี้จะรันมาโครทุกครั้งที่คุณเปิดไฟล์ Excel
เปิด Task Scheduler
ใช้เครื่องมือนี้ขอให้ Windows เปิดสเปรดชีทอัตโนมัติทุกช่วงเวลาที่กำหนด ซึ่งจะทำให้มาโครทำงาน ส่งอีเมลออกไป
เลือก Create Basic Task... จากเมนู Action ทำตามวิซาร์ดจนถึงหน้าจอ Action
เลือก Start a program คลิก Next
ใช้ปุ่ม Browse หาตำแหน่ง Microsoft Excel บนคอมพิวเตอร์ หรือคัดลอกวางพาธลงในช่อง Program/script
จากนั้น ใส่พาธของเอกสาร Excel ของคุณลงในช่อง Add arguments
เสร็จสิ้นวิซาร์ด การจัดตารางเวลาพร้อมใช้งาน
ควรทดสอบโดยตั้งเวลาให้ทำงานในอีกไม่กี่นาที แล้วแก้ไขงานเมื่อยืนยันว่าใช้งานได้แล้ว
หมายเหตุ: อาจต้องปรับการตั้งค่า Trust Center ให้มาโครทำงานได้อย่างถูกต้อง
เปิดสเปรดชีท ไปที่ File > Options > Trust Center
คลิก Trust Center Settings ในหน้าจอถัดไป ตั้ง radio dial เป็น Never show information about blocked content
ทำให้ Microsoft Excel ทำงานให้คุณ
Microsoft Excel เป็นเครื่องมือทรงพลังมาก แต่การเรียนรู้ให้ใช้ประโยชน์สูงสุดอาจดูน่ากลัว หากต้องการเชี่ยวชาญจริงๆ คุณต้องคุ้นเคยกับ VBA ซึ่งไม่ใช่งานง่าย
อย่างไรก็ตาม ผลลัพธ์พูดได้ด้วยตัวมันเอง ด้วยประสบการณ์ VBA นิดหน่อย คุณจะทำให้ Excel ทำงานพื้นฐานอัตโนมัติ ได้เวลาทำงานที่สำคัญกว่า
ใช้เวลาสร้างความเชี่ยวชาญ VBA แต่คุณจะเห็นผลลัพธ์หากอดทนอยู่กับมัน
จุดเริ่มต้นที่ดีคือบทช่วยสอน VBA ใน Excel ของเรา เมื่อจบแล้ว สคริปต์ส่งอีเมลจาก Excel นี้จะดูเหมือนเล่นเกมส์เด็กๆ
