Consulting

Results 1 to 6 of 6

Thread: VBA copy to Outlook

  1. #1
    VBAX Newbie
    Joined
    Nov 2018
    Posts
    2
    Location

    VBA copy to Outlook

    im trying to make so certain range copy into outlook when cells change value. i can get the first part of the code to work, cell changes value. but i cannot figure out how to get the cells to copy. everything i have tried has returned a blank email(including deleting the string body)

    example of what i am trying accomplish:

    cell H5 changes value--copy\paste value C5:J5.
    cell H6 changes value--copy\paste value C6:J6.
    and so forth down the spreadsheet

    Private Sub Worksheet_Change(ByVal Target As Range)
    
        Dim xRgSel As Range
        Dim xOutApp As Object
        Dim xMailItem As Object
        Dim StrBody As String
        Dim xRngCop As Range
        
        On Error Resume Next
        StrBody = "Loose Parts Request"
        
                    
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Set xrg = Range("$H5:$H99999")
        Set xRgSel = Intersect(Target, xrg)
        Set xRngCop = Range("$C5:$J99999")
        ActiveWorkbook.Save
        If Not xRgSel Is Nothing Then
            Set xOutApp = CreateObject("Outlook.Application")
            Set xMailItem = xOutApp.CreateItem(0)
           
            With xMailItem
                .To = ""
                .Subject = "Loose Parts Request"
                .Body = StrBody
                
                
     
                
                .Display
            End With
            Set xRgSel = Nothing
            Set xOutApp = Nothing
            Set xMailItem = Nothing
        End If
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    End Sub

  2. #2
    VBAX Newbie
    Joined
    Nov 2018
    Posts
    2
    Location
    i am beginning to think this is impossible to achieve

  3. #3
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    566
    Location
    No it's probably not possible within a worksheet change event. Make a separate sub and call it from the worksheet change event. Better yet code anything other than a worksheet change event... they are often recursive and produce unexpected results. I'm sure if U searched this site (including the knowledge base) there is likely a ready made code to achieve your objective. HTH. Dave

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,626
    Location
    Change is by a manual change for the most part. If it is a change in formula value, that is another matter.

    Another consideration is to capture multiple "changes" at once. e.g. Copy and pasted the value of 5 to cells H5, H7:H12. If it is always a one row in column H at a time, that is a more simple matter.

    If multiple changes, I would make the macro copy the rows in each column H that changed to a scratch sheet. That range would then be emailed.

    In all of these cases, most would use Ron de Bruin's RangeToHTML() routine for the .htmlBody. https://www.rondebruin.nl/win/s1/outlook/mail.htm

  5. #5
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    566
    Location
    Thanks for the info Ken. Setting up a separate sheet area for output is almost where U should start a project re. "copy the rows in each column H that changed to a scratch sheet. That range would then be emailed.".... seems like an easy resolution. So does the change in formula value create a sheet event? Dave

  6. #6
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,626
    Location
    If the change is by a formula, one uses a Calculate event and looks at Precidents. The rangers in the formula governs the solution. Attach a short simple file to get the best help.

Posting Permissions

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