Hello,
I have a slight problem. Whenever I run the code below I get an error Message: "Compile Error ByRef argument type mismatch" And the Sheet9 (marked in red here) is marked yellow in the debugging mode.
Obviously my code there is not valid but how to modify it so that it will work?
Just in case it is not Clear what I want to achieve is simply that I want the Macro to check IF there is a Sheet9 (IF it exists in the WorkBook) and if YES then add it as a PDF attachement as well. If it does NOT exists then go on With the macro.
Sub SaveAsPDF(ws As Worksheet, FullPath As String)
FullPath = ThisWorkbook.Path & "\" & ws.Name & ".PDF"
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=FullPath
End Sub
Sub Mail_Workbook_1()
Dim OutApp As Object
Dim OutMail As Object
Dim Signature As String
Dim FullPath As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.Display
End With
Signature = OutMail.htmlbody
On Error Resume Next
With OutMail
.To = Range("D10").Value
.CC = ""
.BCC = ""
.Subject = "Papirer Bestilling"
.htmlbody = "<BR>test" & Signature
If Sheet3.Visible = xlSheetVisible Then
SaveAsPDF Sheet3, FullPath
.Attachments.Add FullPath
End If
If Sheet4.Visible = xlSheetVisible Then
SaveAsPDF Sheet4, FullPath
.Attachments.Add FullPath
End If
If Sheet6.Visible = xlSheetVisible Then
SaveAsPDF Sheet6, FullPath
.Attachments.Add FullPath
End If
If Sheet7.Visible = xlSheetVisible Then
SaveAsPDF Sheet7, FullPath
.Attachments.Add FullPath
End If
If Sheet8.Visible = xlSheetVisible Then
SaveAsPDF Sheet8, FullPath
.Attachments.Add FullPath
End If
If WorksheetExists = Sheets(Sheet9) Then
SaveAsPDF Sheet9, FullPath
.Attachments.Add FullPath
End If
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Bookmarks