VBA code to email the document

[megha]

Hello there https://www.mrexcel.com/forum/images/smilies/icon_smile.gif

I am not that good with macro and need your help. I'm using the following code to email the form. It is working fine however I would like to have two modification to my VBA code:

1) how do I add multiple email address into this code (that's the easy one, I guess.)
2) This code save the form first then send via email. I want this form to stay blank all the times. It should just send the completed form so the next user can fill out his/her information and click "email" button to send the form via email.

Thank you in advance!


Private Sub CommandButton21_Click()
Dim OL As Object
Dim EmailItem As Object
Dim Doc As Document

Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Doc = ActiveDocument
Doc.Save

With EmailItem
.Subject = "Waste Collection Request Form"
.Body = "Please see attached form"
.To = "mega77@xx.com"

.cc = "meghan77@xx.com"
'.Importance = olImportanceNormal 'Or olImprotanceHigh Or olImprotanceLow
.Attachments.Add Doc.FullName
.Send
End With

Application.ScreenUpdating = True

Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing
MsgBox "Your form has been emailed. Please check your sent item for a copy"
ThisDocument.Close SaveChanges:=False

End Sub

[arron]

hey there,
i have something that may work for you. its entirely separate from your code, but if you are willing to make the switch it has always worked for me. this code works by running down a list to find a name, the email address in the cell next to it and sending the file in the cell next to that. it can also send multiple files to multiple different individuals at once. you just need to include them in the list. let me know what you think.
for whatever reason, i couldn't insert my code directly so here is a word doc with it

19528

[arron]

this will also allow you to just drop in or remove files with ease