Dear all,
I have a workbook with four sheets. My first sheet is a data entry sheet with 3 command buttons. Based upon certain data entry, one of these buttons is used to send the data to an Issues_Log sheet. My code works fine. What I wish to do at the same time as this Command Button macro runs is to additionally send the updated sheet, as an email attachment, in outlook, ideally this will prepopulate to email address, subject and basic text (Hi this is...). Is there someway to have a combined macro assigned to my button such that it updates the Issues_Log sheet firstly, then copies this sheet as an attachment and sends in a prepopulated Outlook mail. I have pasted my codes. Many thanks
Sub EmailIssues()
ActiveWorkbook.SendMail _
Recipients:=(Range("E5").Value), _
Subject:="*IMPORTANT - ISSUES*" & " - " & Range("A1") & " - " & Range("B3") & " - " & Range("B2")
MsgBox "Assurances Submitted - Issues Reported"
Application.DisplayAlerts = False
End Sub
I have a workbook with four sheets. My first sheet is a data entry sheet with 3 command buttons. Based upon certain data entry, one of these buttons is used to send the data to an Issues_Log sheet. My code works fine. What I wish to do at the same time as this Command Button macro runs is to additionally send the updated sheet, as an email attachment, in outlook, ideally this will prepopulate to email address, subject and basic text (Hi this is...). Is there someway to have a combined macro assigned to my button such that it updates the Issues_Log sheet firstly, then copies this sheet as an attachment and sends in a prepopulated Outlook mail. I have pasted my codes. Many thanks
VBA Code:
Sub SumbittoIssues_Log()
Application.ScreenUpdating = False
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
'Set variables for copy and destination sheets
Set wsCopy = Workbooks("HO_Daily_AssurancesWIP.xlsm").Worksheets("Daily_HO")
Set wsDest = Workbooks("HO_Daily_AssurancesWIP.xlsm").Worksheets("Issues_Log")
Worksheets("Daily_HO").Unprotect ("xxxx")
Worksheets("Issues_Log").Unprotect ("xxxx")
wsCopy.Range("C2:C5").Copy
Sheets("Issues_Log").Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial Paste:=xlPasteValues
wsCopy.Range("D8:D24").Copy
Sheets("Issues_Log").Cells(5, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial Paste:=xlPasteValues
Worksheets("Daily_HO").Protect ("xxxx")
Worksheets("Issues_Log").Unprotect ("xxxx")
MsgBox "Entry Submitted"
End Sub
Sub EmailIssues()
ActiveWorkbook.SendMail _
Recipients:=(Range("E5").Value), _
Subject:="*IMPORTANT - ISSUES*" & " - " & Range("A1") & " - " & Range("B3") & " - " & Range("B2")
MsgBox "Assurances Submitted - Issues Reported"
Application.DisplayAlerts = False
End Sub
VBA Code: