Divy Beginner

  • Member since Jul 18th 2022
  • Last Activity:
Posts
1
Points
10
Profile Hits
50
  • 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

    Sheets("Instructions").Activate

    ActiveWindow.SelectedSheets.Delete


    '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