Help with email macro

wilkisa

wilkisa

Well-known Member
Joined
Apr 7, 2002
Messages
660
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
I need help tweaking a macro to create a PDF file out of a set range and then emailing it to a group of people.

I know that I have a conflict in my code between the Application.Dialogs(XLDialogSendMail).show statement and in the ActiveWorkbook.SendMail line of the code. I realize these aren't compatible statements but I don't know which one to fix.

I want the code to automatically put the distribution list name in the email and send it; my user doesn't want to have to put in the listname or user names manually.

My code is as follows:
<html>Private Sub cmdConvertEmail_Click()

' The following code will convert Sheet3 to a PDF file

Dim FileName As String

FileName = SHEET3(Range("B1:G44"), "", True, True)

'Unprotect the worksheet
ActiveSheet.Unprotect

'Convert to PDF
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
"C:\Users\BREADS BAGELS BUNS.pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
'Application.Dialogs(xlDialogSendMail).Show

'Hide the email button button before sending
cmdConvertEmail.Visible = False

'Send the email to group
ActiveWorkbook.SendMail Recipients:="myemail@myaddress.com", Subject:="New business"

'Unhide the email button
cmdEmail.Visible = True

'Reprotect the worksheet
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub</html>

Can someone help me with this?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Okay, I've done more reading and I now have this code:

<html>Sub RangeConvertPDF()

Dim FileName As String

FileName = RangeConvertPDFEmail(Range("B2:G44"), "", True, False)

If FileName <> "" Then
MailPDF FileName, "myname@address.com, mycoworker@address.com, anothercoworker@address.com", "C of A", "Your PDF file is attached"
End If

End Sub

Function RangeConvertPDFEmail(MyVar As Object, FilePathName As String, _
OverwriteFile As Boolean, OpenPDF As Boolean) As String

Dim strFileFormat As String
Dim FName As Variant

strFileFormat = "PDF Files (*.pdf), *.pdf"
FName = Application.GetSaveAsFilename("", filefilter:=strFileFormat, Title:="C of A PDF")

If FName = False Then Exit Function
Else: FName = FilePathName
End If

If OverwriteFile = False Then
If Dir(FName) <> "" Then Exit Function
End If

On Error Resume Next

MyVar.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=FName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=False, _
IgnorePrintAreas:=False, _
OpenAfterpublish:=OpenPDF
On Error GoTo 0

If Dir(FName) <> "" Then RangeConvertPDFEmail = FName
End If

End Function

Function MailPDF(FileNamePDF As String, strTO As String, strSubject As String, strBody As String, _
Send As Boolean)

Dim OLKApp As Object
Dim OLKMail As Object

Set OLKApp = CreateObject("Outlook.application")
Set OLKMail = OLKApp.CreateItem(0)

On Error Resume Next

With OLKMail
.To = strTO
.CC = ""
.BCC = ""
.Subject = strSubject
.Body = strBody
.Attachments.Add FileNamePDF

If Send = True Then
.Send
Else
.Display
End If
End With

On Error GoTo 0

Set OLKMail = Nothing
Set OLKApp = Nothing

End Function
</html>

I get an error "Argument not optional" in the RangeConvertPDF code at the MAILPDF line. I can't get past it to see if everything else runs.

Can someone please help? I have been beating my brains out trying to figure out the sample codes and modify to suit my needs.

Also, I'm sure I'm going to hear from someone telling me to use HTML tags. I assure you the tags are in place on my end but they never seem to work when the post appears.

Thanks!!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Emailing from Excel and automatically attaching pdf
Replies
1
Views
344
dermie_72
D
R
  • Question Question
Compose Email VBA Help
Replies
1
Views
117
Logit
Logit
B
  • Solved
VBA to email recipient if column cells contain value
Replies
6
Views
575
bh24524
B
S
  • Solved
Tweak to VBA email code
Replies
3
Views
794
rollis13
rollis13
I
  • Question Question
Help with VBA script please
Replies
3
Views
418
jolivanes
jolivanes

Forum statistics

Threads
1,224,522
Messages
6,179,299
Members
452,904
Latest member
CodeMasterX

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top