I have spread sheet that employees fill in info and they click a button that attaches and sends the workbook to an email address.
I'm want the entries in the spread sheet that they filled out to clear or be removed when I click the same button that sends the email.
The following VBA code works great to display the email filled in with "TO:, Subject:, Attachment, and a message in the Body of the email. All the employee needs to do is click the send button, which I could remove and have it send automatically.
But my issue is after the email is sent i was hoping in the same code I could make a range of cells (A10 thru G31) clear or remove what the employee entered so its back to a blank form.
This is the code for sending the email with attachment, having trouble figuring this out.
Sub SendMail()
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
Set olApp = CreateObject("Outlook.Application")
Err.Clear
End If
On Error GoTo 0
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.Subject = "Please Enter Consignment Transactions ASAP"
.To = Range("AA1")
.CC = Range("AB1")
.Attachments.Add ThisWorkbook.FullName
.Body = "Please Enter Consignment Transactions ASAP"
.Display
End With
End Sub
Bookmarks