Sub automatic email () 

Dim myOlApp As Object 
Dim myitem As Object 
Dim I As an Integer, j As Integer 
Dim STRG As String 
Dim atts As Object 
Dim mycc As Object 
Dim myfile As String 

The Set myOlApp=CreateObject (" Outlook. Application ") 

'set to Sheet1 working table, can modify the 
With Sheets (" Sheet1 ") 
I=2 

The Do While. Cells (I, 2) & lt;> "
"'set call Outlook to send email 
The Set myitem=myOlApp. The CreateItem (0) 
The Set atts=myitem. Attachments 
'the recipient email address to invoke the data of column 3 mailbox 
Myitem) To=) Cells (I, 3) 
4 'mail title to invoke the name of the second column column header data 
Myitem. Subject=. Cells (I, 2) & amp; "Teacher," & amp; Cells (I, 4) 
'email body, called the second column is the names of the columns and column 4 B is D column email header 
Myitem. Body=. Cells (I, 2) & amp; "Hello, teacher!" & VbNewLine & amp; VbNewLine & amp; VbNewLine & amp; Cells (I, 4) & amp; ", specific see attachment "& amp; VbNewLine & amp; VbNewLine & amp; VbNewLine & amp; "I wish a happy summer vacation!" 

'work in this thin root directory to find the attachment, and attachment file name is the name of the recipient 
Myfile=Dir (ThisWorkbook Path & amp; "" & amp; Cells (I, 2) & amp; ". * ") 

'here's a add a loop to multiple attachments if not find corresponding to the attachment of names, the E-mail has no attachment 
Do Until myfile="" 
Myitem. Attachments. The Add ThisWorkbook. Path & amp; "" & amp; 1 
 myfile.Myfile=Dir 
Loop 

'the following phrase applies to only add an attachment to use, can use, please replace the cycle of the above if you want to cancel the previous comment' 
'If myfile & lt;> "" Then myitem. Attachments. Add ThisWorkbook. Path & amp; "" & amp; 1 
 myfile.
'the preview, if you want to send directly. The display instead. Send 
Myitem. Send 

I=I + 1 
STRG="" 
Loop 
End With 

The Set myitem=Nothing 

End Sub 

CodePudding user response: