2 Things: VBA Formula to Convert 1 excel sheet to .pdf and mail from Outlook & Clear Contents from Cell

[dasmith7]

Hi,
I have scanned the forums, but nothing fits my criteria. I want to know if there is a VBA formula to convert 1 sheet in an Excel workbook to pdf and mail through Outlook. I do not want the code to attempt to SAVE the .pdf to my drive. I only want it to convert the sheet to pdf and create a new email only.

#2: Is there a formula or VBA code that will simply clear the contents of a cell. I have a workbook that employees enter their id and pull there monthly numbers. Once the id is entered, all fields automatically populate. Is there a VBA that i can use to simply clear the cell that has the ID? I want to link the CLEAR VBA to a command button. Once the employee emails their monthly numbers themselves, the employee can press CLEAR to delete their ID and data prior to someone viewing their information.

 

[Worf]

See if this helps:

Sub AttachActiveSheetPDF()  'original code by ZVI
Dim IsCreated As Boolean, i&, PdfFile$, OutlApp As Object
' Define PDF filename
PdfFile = ActiveWorkbook.FullName
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"
With ActiveSheet    ' or activeworkbook
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
' Use already open Outlook if possible
On Error Resume Next
Set OutlApp = GetObject(, "Outlook.Application")
If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
End If
OutlApp.Visible = True
On Error GoTo 0
With OutlApp.CreateItem(0)
    .Subject = "Quarterly Report"
    .To = "bugs.bunny@hotmail.com"
    '    .CC = "..." ' <-- Put email of 'copy to' recipient here
    .Body = "Hi," & vbLf & vbLf & "The report is attached in PDF format." & vbLf & vbLf & _
    "Regards," & vbLf & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
    On Error Resume Next    ' try to send
    .Send
    Application.Visible = True
    If Err Then
         MsgBox "E-mail was not sent", vbExclamation
    Else
         MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0
End With
Kill PdfFile
' Quit Outlook if it was created by this code
If IsCreated Then OutlApp.Quit
Set OutlApp = Nothing
End Sub