Home

Send email from Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-633264%22%20slang%3D%22en-US%22%3ESend%20email%20from%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-633264%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%20I%20would%20like%20an%20email%20sending%20when%20the%20content%20of%20any%20cell%20in%20a%20range%20of%20cells%20change.%20I%20found%20a%20VBA%20script%20(I%20have%20no%20VBA%20experience)%20on%20a%20website%20and%20that%20does%20the%20first%20part%2C%20when%20a%20cell's%20content%20changes%20it%20open%20an%20email%20with%20the%20message%20saying%20which%20cell%20has%20changed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20then%20have%20to%20click%20send%2C%20when%20I%20actually%20want%20it%20to%20go%20without%20intervention.%20I%20also%20need%20it%20to%20go%20independent%20of%20someone%20having%20Outlook%20open%2C%20so%20if%20someone%20else%20changes%20the%20content%2C%20then%20it%20is%20generated%20and%20sent%20from%20their%20Outlook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20VBA%20I%20am%20using%20which%20as%20I%20say%20opens%20up%20an%20email%20is%20below.%20Any%20help%20would%20be%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3E'Updated%20by%20Extendoffice%202017%2F9%2F12%3CBR%20%2F%3EDim%20xRgSel%20As%20Range%3CBR%20%2F%3EDim%20xOutApp%20As%20Object%3CBR%20%2F%3EDim%20xMailItem%20As%20Object%3CBR%20%2F%3EDim%20xMailBody%20As%20String%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3EApplication.DisplayAlerts%20%3D%20False%3CBR%20%2F%3ESet%20xRg%20%3D%20Range(%22AA3%3AAD100%22)%3CBR%20%2F%3ESet%20xRgSel%20%3D%20Intersect(Target%2C%20xRg)%3CBR%20%2F%3EActiveWorkbook.Save%3CBR%20%2F%3EIf%20Not%20xRgSel%20Is%20Nothing%20Then%3CBR%20%2F%3ESet%20xOutApp%20%3D%20CreateObject(%22Outlook.Application%22)%3CBR%20%2F%3ESet%20xMailItem%20%3D%20xOutApp.CreateItem(0)%3CBR%20%2F%3ExMailBody%20%3D%20%22Cell(s)%20%22%20%26amp%3B%20xRgSel.Address(False%2C%20False)%20%26amp%3B%20_%3CBR%20%2F%3E%22%20in%20the%20worksheet%20'%22%20%26amp%3B%20Me.Name%20%26amp%3B%20%22'%20were%20modified%20on%20%22%20%26amp%3B%20_%3CBR%20%2F%3EFormat%24(Now%2C%20%22mm%2Fdd%2Fyyyy%22)%20%26amp%3B%20%22%20at%20%22%20%26amp%3B%20Format%24(Now%2C%20%22hh%3Amm%3Ass%22)%20%26amp%3B%20_%3CBR%20%2F%3E%22%20by%20%22%20%26amp%3B%20Environ%24(%22username%22)%20%26amp%3B%20%22.%22%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20xMailItem%3CBR%20%2F%3E.To%20%3D%20%22%20xxxxx%40xxx.xx%22%3CBR%20%2F%3E.Subject%20%3D%20%22Worksheet%20modified%20in%20%22%20%26amp%3B%20ThisWorkbook.FullName%3CBR%20%2F%3E.Body%20%3D%20xMailBody%3CBR%20%2F%3E.Attachments.Add%20(ThisWorkbook.FullName)%3CBR%20%2F%3E.Display%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3ESet%20xRgSel%20%3D%20Nothing%3CBR%20%2F%3ESet%20xOutApp%20%3D%20Nothing%3CBR%20%2F%3ESet%20xMailItem%20%3D%20Nothing%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EApplication.DisplayAlerts%20%3D%20True%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-633264%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
stevebott
Visitor

Hi All, I would like an email sending when the content of any cell in a range of cells change. I found a VBA script (I have no VBA experience) on a website and that does the first part, when a cell's content changes it open an email with the message saying which cell has changed.

 

However, I then have to click send, when I actually want it to go without intervention. I also need it to go independent of someone having Outlook open, so if someone else changes the content, then it is generated and sent from their Outlook.

 

The VBA I am using which as I say opens up an email is below. Any help would be appreciated.

 

Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 2017/9/12
Dim xRgSel As Range
Dim xOutApp As Object
Dim xMailItem As Object
Dim xMailBody As String
On Error Resume Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xRg = Range("AA3:AD100")
Set xRgSel = Intersect(Target, xRg)
ActiveWorkbook.Save
If Not xRgSel Is Nothing Then
Set xOutApp = CreateObject("Outlook.Application")
Set xMailItem = xOutApp.CreateItem(0)
xMailBody = "Cell(s) " & xRgSel.Address(False, False) & _
" in the worksheet '" & Me.Name & "' were modified on " & _
Format$(Now, "mm/dd/yyyy") & " at " & Format$(Now, "hh:mm:ss") & _
" by " & Environ$("username") & "."

With xMailItem
.To = " xxxxx@xxx.xx"
.Subject = "Worksheet modified in " & ThisWorkbook.FullName
.Body = xMailBody
.Attachments.Add (ThisWorkbook.FullName)
.Display
End With
Set xRgSel = Nothing
Set xOutApp = Nothing
Set xMailItem = Nothing
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Related Conversations