Sending an excel form by email using VBA

[Papa C]

I am a novice with VBA. I need help getting an excel form to be sent by email once it is completed and saved. The form has multiple drop downs and I would like to use the reference cells in sending the email. I currently have 3 working macro's that name and save the file. A macro that clears the contents in the form after it is saved so it can be used again. And another macro that deletes any text boxes that were added to a site map. I would like to send an email to the email address in "F4" once everything is completed. The Drive in red is where the form is stored (it is stored in a file based on the property in "C4"). I have tried multiple macros but none seem to work. I never get an email nor does it indicate any errors.
Let me know if this is enough information.

 

[Logit]

What is the range of cells your sheet is using ? That will need to be entered into the macro.

 

[Papa C]

The range on the form is A1:G107. I want to send the completed PDF in the email, Thank you

 

[Logit]

I believe this will accomplish your goal. I am unable to test here because I do not have my system set for a G Drive.

Paste the following into a Regular Module.

Sheet 1 / Range A1 is set to have : Your Email SUBJECT
Sheet 1 / Range A2 is set to have : Your PDF File Name

Both of the above can be changed to a different location (requires slight edit in macro) or
both can be hard coded in macro code and left unchanged after that.

Option Explicit

Sub sendReminderMail()
ChDir "G:\DEVEL\Site Inspections\Properties\Amherst NH\"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="G:\DEVEL\Site Inspections\Properties\Amherst NH\" & ActiveSheet.Range("A2").Value & ".pdf", OpenAfterPublish:=False

Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object

Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)


With OutLookMailItem
.To = "Chris.Cheney@wsdevelopment.com"
.Subject = ActiveSheet.Range("A1").Value
.Body = "Please see attached PDF Document."
.Attachments.Add "G:\DEVEL\Site Inspections\Properties\Amherst NH\" & ActiveSheet.Range("A2").Value & ".pdf"
'.send
.Display
End With

Set OutLookMailItem = Nothing
Set OutLookApp = Nothing

End Sub

 

[Papa C]

Thank you. I will test it tomorrow and let you know.

 

[Papa C]

I believe this will accomplish your goal. I am unable to test here because I do not have my system set for a G Drive.

Paste the following into a Regular Module.

Sheet 1 / Range A1 is set to have : Your Email SUBJECT
Sheet 1 / Range A2 is set to have : Your PDF File Name

Both of the above can be changed to a different location (requires slight edit in macro) or
both can be hard coded in macro code and left unchanged after that.

Option Explicit

Sub sendReminderMail()            [I][B]Can this be added to run with the other 3 existing macros using a Call Macro?[/B][/I]
ChDir "G:\DEVEL\Site Inspections\Properties\[I]Amherst NH[/I]\"    [B][I]The "Properties" folder in the Site Inspections folder had about 25 properties. Can this draw from the range in "C4" of the form? Amherst NH is just 1 property[/I][/B]
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="G:\DEVEL\Site Inspections\Properties\[I]Amherst NH[/I]\" & ActiveSheet.Range("A2").Value & ".pdf", OpenAfterPublish:=False [B][I]Wherever you have Amherst can that be the range in "C4"[/I][/B]

Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object

Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)


With OutLookMailItem
.To = "Chris.Cheney@wsdevelopment.com"   The email address would need to be from the range in "F6" of the form
.Subject = ActiveSheet.Range("A1").Value
.Body = "Please see attached PDF Document."
.Attachments.Add "G:\DEVEL\Site Inspections\Properties\[B][I]Amherst NH[/I][/B]\" & ActiveSheet.Range("A2").Value & ".pdf"  [I][B]This would need to be Range in "C4" [/B][/I]
[B][I]'[/I][/B].send
.Display
End With

Set OutLookMailItem = Nothing
Set OutLookApp = Nothing

End Sub

Thank you so much. It worked but I was hoping to have it automated, based on the information on the form. I have added some comments to your above macro. I don't know if what I am asking can be done. I do appreciate your help with this. Thanks

 

[Logit]

Can this be added to run with the other 3 existing macros using a Call Macro? YES

Can this draw from the range in "C4" of the form? Amherst NH is just 1 property YES, but if you use the range value for C4, "Amherst, NH" is all that you will see ... there won't be a DIR CHANGE.

ActiveSheet.Range("A2").Value Change that <--- to ---> ActiveSheet.Range("C4").Value

Change this ---> .To="Chris.Cheney@wsdevelopment.com" The email address would need to be from the range in"F6"of the form ..... to :
.To= ActiveSheet.Range("F6").Value

Change this ---> .Attachments.Add "G:\DEVEL\Site Inspections\Properties\Amherst NH\" & ActiveSheet.Range("A2").Value & ".pdf"
.Attachments.Add "G:\DEVEL\Site Inspections\Properties\Amherst NH\" & ActiveSheet.Range("C4").Value & ".pdf"


I believe my above answers are accurate based on my understanding of what you are wanting to accomplish.

 

[Papa C]

Sub SendEmail()

ChDir "G:\DEVEL\Site Inspections\Properties\" & ActiveSheet.Range("C4").Value
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="G:\DEVEL\Site Inspections\Properties\" & ActiveSheet.Range("C4").Value & ActiveSheet.Range("A2").Value & ".pdf", OpenAfterPublish:=False

Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim myAttachments As Object

Set OutLookApp = CreateObject("Outlook.application")
Set OutLookMailItem = OutLookApp.CreateItem(0)


With OutLookMailItem
.To = ActiveSheet.Range("F4").Value
.Subject = ActiveSheet.Range("A1").Value
.Body = "Please see attached PDF Document."
.Attachments.Add "G:\DEVEL\Site Inspections\Properties\" & ActiveSheet.Range("C4").Value & ActiveSheet.Range("A2").Value & ".pdf"
.send
.Display
End With

Set OutLookMailItem = Nothing
Set OutLookApp = Nothing

End Sub


This is how I adjusted your macro. Amherst is just 1 property of many, so I eliminated the reference to Amherst. Here is the code I used to save the file. Maybe this will help in your coding.

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("C3").Value & Format(Range("C5"), "yyyy_mm_dd") & "_" & Range("C4") & " " & "Site Inspection Report" & ".pdf"

 

[Logit]

I don't understand what you mean by 'having it automated'.

 

[Papa C]

Can the email be sent automatically or does someone have to press the send button when the email pops up?