Adding a Log from user entered comments

[ChaosPup]

Hi all, I have a short bit of code I'd like to expand upon, hoping someone can point me in the right direction. I have a sheet with several basic macro buttons. Each time one of the macro buttons is clicked it opens an InputBox for the user to enter any additional comments they wish, then sends an automated email to a predefined list of addresses, and appends the extra comments to the end of the email. As it is, it works fine. See code below (sorry about all the X’s!)

Sub XXXX()

'Set email objects
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim combody As String

'Set up InputBox for comments
combody = InputBox("Please enter any comments", "Comments")

'Set up Outlook
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

'Create Mail in Outlook
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Hello," & vbNewLine & vbNewLine & _
          "XXXX" & vbNewLine & vbNewLine & _
          "**********THIS EMAIL HAS BEEN AUTOMATICALLY GENERATED, PLEASE DO NOT RESPOND**********"

On Error Resume Next
With OutMail
    .To = "XXXX"
    .CC = ""
    .BCC = ""
    .Subject = "XXXX"
    .Body = strbody & vbNewLine & vbNewLine & "COMMENTS - " & combody
    'You can add a file like this
    '.Attachments.Add ("C:\test.txt")
    .Send   'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

MsgBox ActiveCell.Value & vbNewLine & _
"NOTIFICATION SENT"

End Sub

What I’d like to add is this – after the user has entered their comments and pressed ‘OK’, the comment(s) that were entered are entered onto a ‘LOG’ worksheet (already created within the workbook), along with the date and time. The next time someone uses the workbook and presses one of the macros, it adds those comments to the log on the next row down, as so on.

Can anyone advise how I could add the 'combody' content to the LOG sheet? Thanks!!

 

[Tupe77]

Did you mean something like this?

Sub XXXX()

'Set email objects
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim combody As String

' Variables
Dim wsLOG As Worksheet: Set wsLOG = Worksheets("LOG")
Dim EmptyRow As Range


'Set up InputBox for comments
combody = InputBox("Please enter any comments", "Comments")

'Set up Outlook
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

'Create Mail in Outlook
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Hello," & vbNewLine & vbNewLine & _
          "XXXX" & vbNewLine & vbNewLine & _
          "**********THIS EMAIL HAS BEEN AUTOMATICALLY GENERATED, PLEASE DO NOT RESPOND**********"

On Error Resume Next
With OutMail
    .To = "XXXX"
    .CC = ""
    .BCC = ""
    .Subject = "XXXX"
    .Body = strbody & vbNewLine & vbNewLine & "COMMENTS - " & combody
    'You can add a file like this
    '.Attachments.Add ("C:\test.txt")
    .Send 'or use.Display
End With
On Error GoTo 0

' Write
Set EmptyRow = wsLOG.Range("A" & wsLOG.Cells(Rows.Count, "A").End(xlUp).Row + 1).Resize(1, 3)
EmptyRow.Cells(1).Value2 = combody: EmptyRow.Cells(2).Value2 = Date: EmptyRow.Cells(3).Value2 = Time 'combody, date,time

Set OutMail = Nothing
Set OutApp = Nothing

MsgBox ActiveCell.Value & vbNewLine & _
"NOTIFICATION SENT"

End Sub

 

[ChaosPup]

Did you mean something like this?

Sub XXXX()

'Set email objects
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim combody As String

' Variables
Dim wsLOG As Worksheet: Set wsLOG = Worksheets("LOG")
Dim EmptyRow As Range


'Set up InputBox for comments
combody = InputBox("Please enter any comments", "Comments")

'Set up Outlook
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

'Create Mail in Outlook
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Hello," & vbNewLine & vbNewLine & _
          "XXXX" & vbNewLine & vbNewLine & _
          "**********THIS EMAIL HAS BEEN AUTOMATICALLY GENERATED, PLEASE DO NOT RESPOND**********"

On Error Resume Next
With OutMail
    .To = "XXXX"
    .CC = ""
    .BCC = ""
    .Subject = "XXXX"
    .Body = strbody & vbNewLine & vbNewLine & "COMMENTS - " & combody
    'You can add a file like this
    '.Attachments.Add ("C:\test.txt")
    .Send 'or use.Display
End With
On Error GoTo 0

' Write
Set EmptyRow = wsLOG.Range("A" & wsLOG.Cells(Rows.Count, "A").End(xlUp).Row + 1).Resize(1, 3)
EmptyRow.Cells(1).Value2 = combody: EmptyRow.Cells(2).Value2 = Date: EmptyRow.Cells(3).Value2 = Time 'combody, date,time

Set OutMail = Nothing
Set OutApp = Nothing

MsgBox ActiveCell.Value & vbNewLine & _
"NOTIFICATION SENT"

End Sub

That is fantastic. Thanks very much!

 

[Tupe77]

Nice to hear we were able to help and thanks for the feedback!