# [SOLVED] Macro to send email with PDF. MSExcel



## barcas84 (Nov 2, 2010)

Hi, I have this macro that sends two emails from outlook with a PDF attached. I used this and it's been working fine, now I change the information of the sheets to do a macro that send different PDF's and is not working, gives an error:

 Run time error '1004'
Copy method of Worksheet class failed

When I click in debug, it highlights ws.Copy

This is the macro:

Sub SendPDFKEVLARViaOutlook()
Sheet33.Visible = True
Dim StringTo As String, StringCC As String, StringBCC As String, Fname As String, Fname1 As String
Dim wb1 As Workbook, wb2 As Workbook, sh As Worksheet
Dim DefPath As String
Dim olApp As Object
Dim olMail As Object
Dim FileExtStr As String

If Len(ThisWorkbook.Path) = 0 Then
MsgBox "This macro will only work if the file is Saved once.", 48, "Mail PDF Outlook"
Exit Sub
End If

'Set folder where we save the temporary files
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

Fname1 = DefPath & "QUOTE"
Set wb1 = ActiveWorkbook
Set ws = wb1.Worksheets(33)

ws.Copy

Set wb2 = ActiveWorkbook
Fname = DefPath & "tempworkbook.xlsx"
wb2.SaveCopyAs Fname

On Error Resume Next
wb2.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=Fname1, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
On Error GoTo 0
wb2.Close Fale
Set wb2 = Nothing
'--------------------------------------------------------'
Fname2 = DefPath & "Calculations"
Set wb1 = ActiveWorkbook
Set ws = wb1.Worksheets(28)

ws.Copy

Set wb2 = ActiveWorkbook
Fname = DefPath & "tempworkbook.xlsx"
wb2.SaveCopyAs Fname

On Error Resume Next
wb2.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=Fname2, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
On Error GoTo 0
wb2.Close False
Set wb2 = Nothing
'Set reference to Outlook and turn off ScreenUpdating and Events
Set olApp = CreateObject("Outlook.Application")
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Set variables for parts of the email
'You may need to change these
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set olMail = Nothing

StringTo = wb1.Worksheets(32).Range("B2").Value
StringCC = wb1.Worksheets(32).Range("B3").Value
StringSubject = wb1.Worksheets(32).Range("B4").Value
StringBody = wb1.Worksheets(32).Range("B5").Value
StringAttach = wb1.Worksheets(32).Range("B6").Value
StringAttach2 = wb1.Worksheets(32).Range("B7").Value
'Set email parts to variables
'On Error Resume Next
Set olMail = olApp.CreateItem(0)
With olMail
.To = StringTo
.CC = StringCC
.Subject = StringSubject
.Body = StringBody
If StringAttach <> 0 Then .Attachments.Add StringAttach
If StringAttach2 <> 0 Then .Attachments.Add StringAttach2
.Attachments.Add Fname1 & ".pdf"
.Display

Set olMail = Nothing
End With

Set olMail = Nothing

StringTo = wb1.Worksheets(32).Range("F2").Value
StringSubject = wb1.Worksheets(32).Range("B4").Value
StringBody = wb1.Worksheets(32).Range("F5").Value
StringAttach = wb1.Worksheets(32).Range("F6").Value

'Set email parts to variables
'On Error Resume Next
Set olMail = olApp.CreateItem(0)
With olMail
.To = StringTo
.Subject = StringSubject
.Body = StringBody
If StringAttach <> 0 Then .Attachments.Add StringAttach
.Attachments.Add Fname2 & ".pdf"
.Attachments.Add Fname1 & ".pdf"
.Send

Set olMail = Nothing

End With
Sheet33.Visible = False
End Sub

Thanks!


----------



## RSpecianjr (Jan 20, 2010)

*Re: Macro to send email with PDF. MSExcel*

Hey,

I don't remember if the worksheet you want to copy has to be visible or not. If it is hidden, try making it visible then running the script. We can automate the hiding/unhiding in the macro if that is the case.

Also, try:

Add:

ws.activate

Before:

ws.copy 

Ciao,

Robert


----------



## barcas84 (Nov 2, 2010)

*Re: Macro to send email with PDF. MSExcel*

Hey Robert,

Yeah the Sheet is hidden, but the macro already includes code to unhide and hide again...I tried to unhide it manually anyway and it didn't work.
I tried the ws.Activate, and still has the same error...

I don't know what is the issue, because I just copied it and pasted it changing the Sheets, that's all... and the other one works!


----------



## RSpecianjr (Jan 20, 2010)

*Re: Macro to send email with PDF. MSExcel*

Can you post the workbook?


----------



## barcas84 (Nov 2, 2010)

*Re: Macro to send email with PDF. MSExcel*

Yeah, I guess I could, I have to delete some information first, so tomorrow morning I will post it.


----------



## RSpecianjr (Jan 20, 2010)

*Re: Macro to send email with PDF. MSExcel*

k (plus four more characters)


----------



## barcas84 (Nov 2, 2010)

*Re: Macro to send email with PDF. MSExcel*

Solved!!!

It was a weird thing... When I was working on the sheets, I moved them around, and seems like the number of the sheet doesn't change in the list on the right when you are working on VBA. 

So I had to unhide all of them (36) manually and count the position...and it worked, but then when I hide them again, it was again giving me the same error...

So this is what happened, there is a line of code to unhide the sheet and then one at the end to hide it again. Well seems like in that line of code I had to put the number of the sheet that appears in the panel on the right, and in the rest of the macro, I had to put the real number of the sheet (that changes because I moved it)...

Looks like the macro unhides for example sheet 28, then it places it in position 9 (because I moved it there), and then it does the PDF and sends the email of the Sheet 9!!

I don't know why it doesn't change the number of the sheet in the panel...but well next time I'll use the name of the sheets instead of the numbers!


----------



## RSpecianjr (Jan 20, 2010)

*Re: Macro to send email with PDF. MSExcel*

Glad it got worked out! I figured it was something to do with the hiding, but I wouldn't have been able to figure that out without looking at it.

Thanks for posting the solution.

Regards,

Robert D. Specian Jr.


----------



## barcas84 (Nov 2, 2010)

*Run-time error '1004': Copy Method of Worksheet Class failed*

Hi, I'm having the following error:

Run-time error '1004': 
Copy Method of Worksheet Class failed

If you click in debug, it points to ws.copy

In this macro:

Sub SendPDFViaOutlook()
Sheet14.Visible = True
Dim StringTo As String, StringCC As String, StringBCC As String, Fname As String, Fname1 As String
Dim wb1 As Workbook, wb2 As Workbook, sh As Worksheet
Dim DefPath As String
Dim olApp As Object
Dim olMail As Object
Dim FileExtStr As String

If Len(ThisWorkbook.Path) = 0 Then
MsgBox "This macro will only work if the file is Saved once.", 48, "Mail PDF Outlook"
Exit Sub
End If

'Set folder where we save the temporary files
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

Fname1 = DefPath & "Quote"
Set wb1 = ActiveWorkbook
Set ws = wb1.Worksheets(3)

ws.Copy

Set wb2 = ActiveWorkbook
Fname = DefPath & "tempworkbook.xlsx"
wb2.SaveCopyAs Fname

On Error Resume Next
wb2.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=Fname1, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
On Error GoTo 0
wb2.Close Fale
Set wb2 = Nothing
'--------------------------------------------------------'
Fname2 = DefPath & "Calculations"
Set wb1 = ActiveWorkbook
Set ws = wb1.Worksheets(1)

ws.Copy

Set wb2 = ActiveWorkbook
Fname = DefPath & "tempworkbook.xlsx"
wb2.SaveCopyAs Fname

On Error Resume Next
wb2.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=Fname2, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
On Error GoTo 0
wb2.Close False
Set wb2 = Nothing
'Set reference to Outlook and turn off ScreenUpdating and Events
Set olApp = CreateObject("Outlook.Application")
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Set variables for parts of the email
'You may need to change these
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set olMail = Nothing

StringTo = wb1.Worksheets(2).Range("B2").Value
StringCC = wb1.Worksheets(2).Range("B3").Value
StringSubject = wb1.Worksheets(2).Range("B4").Value
StringBody = wb1.Worksheets(2).Range("B5").Value
StringAttach = wb1.Worksheets(2).Range("B6").Value
StringAttach2 = wb1.Worksheets(2).Range("B7").Value
'Set email parts to variables
'On Error Resume Next
Set olMail = olApp.CreateItem(0)
With olMail
.To = StringTo
.CC = StringCC
.Subject = StringSubject
.Body = StringBody
If StringAttach <> 0 Then .Attachments.Add StringAttach
If StringAttach2 <> 0 Then .Attachments.Add StringAttach2
.Attachments.Add Fname1 & ".pdf"
.Display

Set olMail = Nothing
End With

Set olMail = Nothing

StringTo = wb1.Worksheets(2).Range("F2").Value
StringSubject = wb1.Worksheets(2).Range("B4").Value
StringBody = wb1.Worksheets(2).Range("F5").Value
StringAttach = wb1.Worksheets(2).Range("F6").Value

'Set email parts to variables
'On Error Resume Next
Set olMail = olApp.CreateItem(0)
With olMail
.To = StringTo
.Subject = StringSubject
.Body = StringBody
If StringAttach <> 0 Then .Attachments.Add StringAttach
.Attachments.Add Fname2 & ".pdf"
.Attachments.Add Fname1 & ".pdf"
.Send

Set olMail = Nothing

End With
Sheet14.Visible = False
End Sub

The macro is been working fine for like two months, now it doens't work and shows that error. I've been looking around on Internet, and seems that is a common issue when running a macro that copies a Worksheet... I found this Microsoft article Copying worksheet programmatically causes run-time error 1004 in Excel that offers a solution for Excel 2007. I'm running 2010 and I don't even know how to apply that solution they offer in the 2007...

Help!!!!!!!!!!!

P.S. I can't post the workbook because that macro is linked to other files in my computer in order to run, so I will have to upload the whole folder, which is more than 400 MB, which I think is too big to upload here, right?


----------



## RSpecianjr (Jan 20, 2010)

*Re: Macro to send email with PDF. MSExcel*

Hey again Barcas,

Yeah, I think thats a bit too big = P.

Best guess of the top of my head is give it focus. Make sure the workbook has focus, then give the worksheet focus. After that it SHOULD work.

Insert

wb1.activate
ws.activate

right before the copy line. See if that helps. I'm in the middle of cooking lasagna, so I can't really dive into it right now. If that doesn't work, I'll take a look at it tomorrow.

Ciao,

Robert


----------



## barcas84 (Nov 2, 2010)

*Re: Macro to send email with PDF. MSExcel*

Hummmm Lasagna!!!!!! Hope it came out good!!!!!

That didn't work, is still having the same error... On Internet they say that if you've used the macro to many times without saving and closing it comes out with the error... I don't really understand why that happens, and in the Microsoft article I don't understand their solution either....

Robert I need help!!!!!! 

Hugs!

Marco.


----------



## barcas84 (Nov 2, 2010)

*Re: Macro to send email with PDF. MSExcel*

Yeah, the sheet is hidden, but in the macro you already put to unhide it and hide after automatically . This time, is not a problem with the hidding thing, this time I didn't touch or change anything and it just suddenly stopped working...


----------



## RSpecianjr (Jan 20, 2010)

*Re: Macro to send email with PDF. MSExcel*

Oh man, alright, I hope we don't have to go through the saving process. We can try using this:

.Worksheets(1).copy

instead of 

ws.copy

If that doesn't work, it looks like we will have to save the file right before copying the worksheet. Is it okay to save the file under the name it is opened with or do would this be bad?

Regards,

Robert D. Specian Jr.


----------



## barcas84 (Nov 2, 2010)

*Re: Macro to send email with PDF. MSExcel*

Hey! Yeah it's something with the saving process, or at least that's what that Microsoft article said...

Anyway, the worsheets(1).copy didn't work...

About saving, it would be a problem because there are a lot of people using it, and you know how people is, they sometimes screw something and if the save it it will overwrite the file, and if they have to change the name, it would take too much time... But anyway, I just tried saving the file first, and still gives me the error....

Help Robert! Help!!!!!!!


----------



## barcas84 (Nov 2, 2010)

*Re: Macro to send email with PDF. MSExcel*

Oh man I tried to post the workbook but it's 6.43 MB and the maximum it's 5...!!!!:upset:


----------



## RSpecianjr (Jan 20, 2010)

*Re: Macro to send email with PDF. MSExcel*

Hey,

The save has to be in the macro, it can't be a manual process. I am trying to think of how we could autosave it to a different location, then when they manually save it have it save to the original location. Like I said, that is going to be a pain. Perhaps someone else has any ideas?

Regards,

Robert D. Specian Jr.


----------



## barcas84 (Nov 2, 2010)

*Re: Macro to send email with PDF. MSExcel*

Why does excel have that error? And why it started now out of the blue?


----------



## Glaswegian (Sep 16, 2005)

*Re: Macro to send email with PDF. MSExcel*

I haven't been through this in great detail, but I don't see where you've defined the variable '*ws*'. I see a variable called '*sh*' but it's not been used anywhere? Something I do is rather than using a string for the Fname variable I would Dimension it as a Variant. 

Not sure if that will help though.


----------



## barcas84 (Nov 2, 2010)

*Re: Macro to send email with PDF. MSExcel*

Hi Glaswegian, thanks for your response. I don't know that much about macros, so if you could copy and paste the macro with that change that you say so I could try it, it would be great!

Thanks you!!


----------



## RSpecianjr (Jan 20, 2010)

*Re: Macro to send email with PDF. MSExcel*

Hey Glaswegian,

I created the macro with parts from another macro in the workbook. They arn't universal though. Good catch, thanks.

Barcas, we can replace:

sh As Worksheet

with

ws As Worksheet

I'm not sure how Variants work, perhaps just:

ws As Variant

Try both of these and see if that helps!

Regards,

Robert


----------



## Glaswegian (Sep 16, 2005)

*Re: Macro to send email with PDF. MSExcel*

OK - it wasn't the complete code...lol

You can also just use

```
Dim ws
```
If you do not specify a data type Excel will automatically create the variable as a Variant. Sometimes allows you a bit more flexibility.


----------



## barcas84 (Nov 2, 2010)

*Re: Macro to send email with PDF. MSExcel*

Hi! Sorry guys, I was on vacation las week...

I tried the three ways, ws As Worksheet, ws As Variant and Dim ws, and I still have the same error...

I remind you, because I think is important, that this macro is been working fine for couple months, and other macros like this one, but with different sheets, are still working..!

So I have not clue what is the problem here....

Thanks!


----------



## barcas84 (Nov 2, 2010)

*Re: Macro to send email with PDF. MSExcel*

Don't know if it would help, but just now, one of the of the other macros that is the same (just with different sheets) is failing...


Sub SendPDFKEVLARViaOutlook()
Sheet33.Visible = True
Dim StringTo As String, StringCC As String, StringBCC As String, Fname As String, Fname1 As String
Dim wb1 As Workbook, wb2 As Workbook, sh As Worksheet
Dim DefPath As String
Dim olApp As Object
Dim olMail As Object
Dim FileExtStr As String

If Len(ThisWorkbook.Path) = 0 Then
MsgBox "This macro will only work if the file is Saved once.", 48, "Mail PDF Outlook"
Exit Sub
End If

'Set folder where we save the temporary files
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

Fname1 = DefPath & "Quote"
Set wb1 = ActiveWorkbook
Set ws = wb1.Worksheets(8)

ws.Copy

Set wb2 = ActiveWorkbook
Fname = DefPath & "tempworkbook.xlsx"
wb2.SaveCopyAs Fname

On Error Resume Next
wb2.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=Fname1, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
On Error GoTo 0
wb2.Close Fale
Set wb2 = Nothing
'--------------------------------------------------------'
Fname2 = DefPath & "Calculations"
Set wb1 = ActiveWorkbook
Set ws = wb1.Worksheets(9)

ws.Copy

Set wb2 = ActiveWorkbook
Fname = DefPath & "tempworkbook.xlsx"
wb2.SaveCopyAs Fname

On Error Resume Next
wb2.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=Fname2, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
On Error GoTo 0
wb2.Close False
Set wb2 = Nothing
'Set reference to Outlook and turn off ScreenUpdating and Events
Set olApp = CreateObject("Outlook.Application")
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Set variables for parts of the email
'You may need to change these
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set olMail = Nothing

StringTo = wb1.Worksheets(7).Range("B2").Value
StringCC = wb1.Worksheets(7).Range("B3").Value
StringSubject = wb1.Worksheets(7).Range("B4").Value
StringBody = wb1.Worksheets(7).Range("B5").Value
StringAttach = wb1.Worksheets(7).Range("B6").Value
'Set email parts to variables
'On Error Resume Next
Set olMail = olApp.CreateItem(0)
With olMail
.To = StringTo
.CC = StringCC
.Subject = StringSubject
.Body = StringBody
If StringAttach <> 0 Then .Attachments.Add StringAttach
.Attachments.Add Fname1 & ".pdf"
.Display

Set olMail = Nothing
End With

Set olMail = Nothing

StringTo = wb1.Worksheets(7).Range("F2").Value
StringSubject = wb1.Worksheets(7).Range("F4").Value
StringBody = wb1.Worksheets(7).Range("F5").Value
StringAttach = wb1.Worksheets(7).Range("F6").Value

'Set email parts to variables
'On Error Resume Next
Set olMail = olApp.CreateItem(0)
With olMail
.To = StringTo
.Subject = StringSubject
.Body = StringBody
If StringAttach <> 0 Then .Attachments.Add StringAttach
.Attachments.Add Fname2 & ".pdf"
.Attachments.Add Fname1 & ".pdf"
.Send

Set olMail = Nothing

End With
Sheet33.Visible = False
End Sub

So what it does is it opens a preview of an email with the Worksheet 9 attached instead of worksheet 8, with no subject, no to, no cc...nothing. And then it gives a run time error:

Run-time error -'2147467259 (80004005)':
Outlook does not recognize one or more names.

And it highlights .Send

And again, this macro is been working fine for couple months and know is giving me this error... I've tried the three ways (ws As workbook, As variant and the Dim ws) and is still not working...

Help!!!!


----------



## barcas84 (Nov 2, 2010)

*Re: Macro to send email with PDF. MSExcel*

Any ideas anybody??


----------



## RSpecianjr (Jan 20, 2010)

*Re: Macro to send email with PDF. MSExcel*

Other than recreating the workbook from scratch... I'm out of ideas = /

Maybe making a new workbook, copying and pasting the data in, then saving the new workbook. Not sure how the macro will work though. Anyone?

Regards,

Robert


----------



## Glaswegian (Sep 16, 2005)

*Re: Macro to send email with PDF. MSExcel*

Unless there is some kind of workbook corruption? Might be worth copying everything to a new workbook, even if it's just to rule out corruption.


----------



## barcas84 (Nov 2, 2010)

*Re: Macro to send email with PDF. MSExcel*

But if I do that, would it happen again in the new workbook?


----------



## Glaswegian (Sep 16, 2005)

*Re: Macro to send email with PDF. MSExcel*

Possibly - but possibly not. I've had workbooks that just fall over for no reason - copy everything to a new workbook and it runs fine. Workbook corruption does happen.


----------



## barcas84 (Nov 2, 2010)

*Re: Macro to send email with PDF. MSExcel*

I'm trying to copy the workbook but it's just too big... If I try to copy a whole sheet it freezes... and there are more than 30 sheets.... So I would have to copy few rows at a time and it would take forever...

Any other ideas? Or is it the only solution you guys see possible?


----------



## barcas84 (Nov 2, 2010)

*Re: Macro to send email with PDF. MSExcel*

Hey guys! I paid a company to fix this issue, this is the macro they did... I don't really understand it but maybe you find it useful!!

Sub SendPDFTRANSViaOutlook()

Dim StringTo As String, StringCC As String, StringBCC As String, Fname As String, Fname1 As String
Dim wb1 As Workbook, wb2 As Workbook, sh As Worksheet
Dim DefPath As String
Dim olApp As Object
Dim olMail As Object
Dim FileExtStr As String

If Len(ThisWorkbook.Path) = 0 Then
MsgBox "This macro will only work if the file is Saved once.", 48, "Mail PDF Outlook"
Exit Sub
End If

'Set folder where we save the temporary files
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

Fname1 = DefPath & "Quote"
Set wb1 = ActiveWorkbook

'Save sheet "EMAILTRANSMISSION" as pdf

Sheet36.Visible = True
Sheet36.Copy
Set wb2 = ActiveWorkbook
Fname = DefPath & "tempworkbook.xlsx"
wb2.SaveCopyAs Fname

On Error Resume Next
wb2.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=Fname1, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
On Error GoTo 0
wb2.Close False
Set wb2 = Nothing
Sheet36.Visible = xlSheetHidden

'--------------------------------------------------------'
Fname2 = DefPath & "Calculations"
Set wb1 = ActiveWorkbook

'Save sheet "PowerTransmission" as pdf

Sheet34.Copy
Set wb2 = ActiveWorkbook
Fname = DefPath & "tempworkbook.xlsx"
wb2.SaveCopyAs Fname

On Error Resume Next
wb2.ExportAsFixedFormat _
Type:=xlTypePDF, _
filename:=Fname2, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
On Error GoTo 0
wb2.Close False
Set wb2 = Nothing

'Set reference to Outlook and turn off ScreenUpdating and Events
Set olApp = CreateObject("Outlook.Application")
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Set variables for parts of the email
'You may need to change these
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Use sheet "MailTransmission": codename Sheet39
Set olMail = Nothing

StringTo = Sheet39.Range("B2").Value
StringCC = Sheet39.Range("B3").Value
StringSubject = Sheet39.Range("B4").Value
StringBody = Sheet39.Range("B5").Value
StringAttach = Sheet39.Range("B6").Value
'Set email parts to variables
'On Error Resume Next
Set olMail = olApp.CreateItem(0)
With olMail
.To = StringTo
.CC = StringCC
.Subject = StringSubject
.Body = StringBody
If StringAttach <> 0 Then .Attachments.Add StringAttach
.Attachments.Add Fname1 & ".pdf"
.Display
End With

Set olMail = Nothing

StringTo = Sheet39.Range("F2").Value
StringSubject = Sheet39.Range("F4").Value
StringBody = Sheet39.Range("F5").Value
StringAttach = Sheet39.Range("F6").Value

'Set email parts to variables
'On Error Resume Next
Set olMail = olApp.CreateItem(0)
With olMail
.To = StringTo
.Subject = StringSubject
.Body = StringBody
If StringAttach <> 0 Then .Attachments.Add StringAttach
.Attachments.Add Fname2 & ".pdf"
.Attachments.Add Fname1 & ".pdf"
.Send
End With
Set olMail = Nothing

End Sub


----------

