Email out with an attachment (reduced size) via Excel using VBA coding

  • Hi there,

    I need your expertise to fix the VBA coding. I am working on a project to send an email out with an attachment (reduced size) via Excel using VBA coding. Currently, I am stuck on the attachment part and the body is not reflecting, the way I like to.

    When I run the macro, the code deletes the extra sheets from the workbook but doesn't attach the modified excel file. Instead of attaching the original excel file including all the sheets to outlook. This information pops up as well - the email address, subject and the body of the email (formating is incorrect). Not sure what I am doing wrong? I would appreciate it if you could please help me. Thanks in advance.

    Here is the code for your review.

    Sub SendMail()

    Dim OutApp As Outlook.Application

    Set OutApp = CreateObject("Outlook.Application")

    Dim OutMail As Outlook.MailItem

    Set OutMail = OutApp.CreateItem(olMailItem)

    'Dim strBody As String

    'Set strBody = ????????

    Sheets(Array("Instructions", "Data 2", "Model", "Sheet2", "Sheet4")).Select



    'Application.ActiveSheet (olMailItem)

    On Error Resume Next

    With OutMail

    OutMail.To = "[email protected]"

    OutMail.Subject = "ABCD"

    OutMail.Body = "<Body style = Font-size:12pt ; font-familt:Calibri>" & _

    "Hi Team, <br><br> I confirm that I have received the attached invoices from the said vendor(s). I have checked and reviewed the invoice to be correct; and the said goods and services listed on the invoice have been received at the XXXX site being managed by JLL in good order, as per the quantity listed on the invoice and in the agreed quality per said XXX ‘s PO or contract with the vendor.<br><br>" & _

    "Best Regards,<br>"

    OutMail.Attachments.Add ActiveWorkbook.FullName

    OutMail.Display ' DISPLAY MESSAGE.

    End With

    On Error GoTo 0

    Set OutMail = Nothing: Set outOutlook = Nothing

    End Sub

  • After you delete the sheets you have to save the workbook before you attach it. Attach retrieves the file from the Windows file system, not from Excel's memory.



    Making the world a better place one fret at a time | | |·| |·| |·| |·| | |:| | |·| |·|

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!