Consulting

Results 1 to 2 of 2

Thread: Email Active workbook with updates made

  1. #1
    VBAX Newbie
    Joined
    Feb 2020
    Location
    Manchester
    Posts
    4
    Location

    Email Active workbook with updates made

    Hi Folks,

    I am new to the world of VBA and slowly understanding more about each aspect of code, however i am not quite there yet.

    I have recently created a proforma document that captures information from an end user. Once completed the end user hits the relevant buttons and this then sends an email to their manager for review before heading off to the relevant department to action the request.
    I have had feedback that the code works and sends the email (great) but the excel attachment does not include the updates that the user has applied before sending. I want to be able to send/forward the active workbook with the changes/updates that the user has applied to the relevant parties.

    I have seen multiple threads that suggest the only way is to temp save the file, email and then delete this and build into vba code. Is there a way around this that you can help with with?

    If not and i use the temp saving options i see on these forums will i have to select a location for the files to be saved. My reason for asking is each user has different creditinals and this form is asscessibale to over 500 users so needs to be generic.

    Any help is appreciated.


    Amy



    This is my current code:
    Private Sub LineManagerSubmit_Click()






    Dim emailApplication As Object
    Dim emailItem As Object


    Set emailApplication = CreateObject("Outlook.Application")
    Set emailItem = emailApplication.CreateItem(0)


    ' Now build the email.


    emailItem.to = Range("C88").Value


    emailItem.CC = Range("C71").Value


    emailItem.Subject = "IE -" & Range("C9 ").Value & " - " & Range("F9").Value


    emailItem.Body = "Hi " & Range("C89").Value & "," & vbCrLf & vbCrLf & "Please find attached details of a new error for review." & vbCrLf & vbCrLf & "Please can you review and share with the appropriate team. " & vbCrLf & vbCrLf & "If you have any questions please ask" & vbCrLf & vbCrLf & "Kind Regards" & vbCrLf & vbCrLf & Range("C7").Value


    MsgBox "Your email has been sent" & vbCrLf & vbCrLf & "This file has not been saved"


    ' Attach current Workbook
    emailItem.Attachments.Add ActiveWorkbook.FullName


    ' Send the Email
    emailItem.Send


    Set emailItem = Nothing
    Set emailApplication = Nothing


    End Sub

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    ' Attach current Workbook
    ActiveWorkbook.Save
    emailItem.Attachments.Add ActiveWorkbook.FullName

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •