Create code that sends emails and creates a log

[cokeoverpepsi123]

Hi everyone,

I have been trying now for 2 days to write a code, and I still have not solved this. What I thought would be easy has been the difficult part - and I just cant find a solution.

In short, I download a report weekly that shows me all details of tickets placed on our pricing website.

I put this into my excel, and excel formulas (NON VBA) cleans or extracts the data that is useful for me.

The goal is to use this data to automatically fill in specific variables on a generic email subject/body send emails from Excel and track responses. I also set it up to have a reminder sent if we have not received feedback within a week. All of this is working perfectly.

There is a 2 huge flaws though, and this is what I need to solve.

The way I have it set up is I have 1 sheet that houses all the Data elements that I update weekly with new tickets. This sheet also contains the VBA code and all the formulas that determine if an email needs to be sent. This sheet has no other function as all the info comes from other better organized sheets.

The other sheet in question is the one my team reviews on a regular basis and contains the responses of the sent emails. This serves two functions - 1 to organize the responses for my team to analyze and 2 - to track if an email was sent, and if a follow up email needs to be sent.

What doesn't work automatically- when I do send the email on the specific ticket (ticket number is the key identifier, Column L), I need to have this number logged on the second sheet This will trigger the aforementioned formulas and would be where my team would go to look at customer feedback.

I could have Sent added to the original line to trigger the VBA to not send again, but the purpose here is twofold as I said above. I need to copy the ticket number anyway to another Feedback sheet, so having it all run based on each other just makes more sense.

Process -

I have a button that sends out emails based on Yes/No in column Q for a time period I select. This works great!

After that is the manual process I am looking to add to the code:

I then take all the ticket numbers the email pretains to and I manually copy them to the first available cell on the Response tab with todays date.

The code would read in simple words:

If Q is "yes" send email below. It would do its loop and send to everyone. THIS IS WORKING

If S is "yes" send 2nd email below. It would do its loop and send to everyone. THIS IS WORKING too.

(here is what I need)

Once those 2 tasks are done,

If Q is "yes" then copy cell L to the first open slot in column A, and todays date in Column B

If S is "yes" then match column L "Follow ups" to Column A, and put "Yes" in column D of the matching row.

This would have to run AFTER the emails are sent because this will turn the trigger off.

* What the first step accomplishes is on first email, it creates a log of it on the Response sheet and starts a 7 day timer to send a reminder.

* the second step says for that ticket, I now sent a reminder, and will turn off further reminders.

Let me know please how I can accomplish this!

Thank you in Advance!!

John

"follow Up" sheet which contains all the original data.

To Upload.xlsm
	K	L		N	O	P	Q		S	T	U	V
1											Start Date	End Date
2											8/1/2024	8/20/2024
3	First Requestor Name	Ticket Number		Follow up Done	response	Follow Up Received?	Send Email?		Send Reminder Email?
4	Name	1235		Yes	HI	Yes	no
5	Name	45678		Yes		No	no
6	Name	45613		Yes		No	no		yes
7	Name	13546876		No			yes
8		123		No			yes
9
10
Follow Ups

Cell Formulas
Range	Formula
O4:O10	O4	=IF(XLOOKUP(L4,'Follow Up Response'!A:A,'Follow Up Response'!E:E,"",0)="","",XLOOKUP(L4,'Follow Up Response'!A:A,'Follow Up Response'!E:E))
P4:P10	P4	=IF(N4="","",IF(N4="No", "", IF(O4="","No","Yes")))
Q4:Q9	Q4	=IF(N4="","",IF(N4="no","yes","no"))
S4:S10	S4	=IF(P4="No",IF(NOW()-R4>=7,"yes",""),"")
N4:N5,N7:N10	N4	=IF(L4="","",IF(M4=1, "Yes", "No"))
Q10	Q10	=IF(N10="","",IF(N10="no","yes",IF(P10="Yes", "no", "yes")))
K8	K8	=IF(J8="","",PROPER(J8))

"Follow Up Response is where the Ticket Number (L) and date email was sent is pasted

To Upload.xlsm
	A	B	C	D	E
1	Ticket Number	Customer	Email Sent Date	Reminder Sent?	Feedback
2	1235	Test	8/22/2024		HI
3	45678	Testa	8/20/2024
4	45613	Testb	8/10/2024
5
6
7
8
9
10
11
12
13
14
15
16
Follow Up Response

Private Sub SendEmailReminders_Click()
    
    Dim cd As Worksheet
    Dim LastRow As Long, r As Long
    Dim xOutApp As Object
    Dim xOutMail As Object
    Dim xMailBody As String
    
    Set cd = ThisWorkbook.Sheets("Follow Ups")
    LastRow = cd.Cells(cd.Rows.Count, "H").End(xlUp).Row
    
    Set xOutApp = CreateObject("Outlook.Application")
    
        For r = 2 To LastRow
        
                If cd.Cells(r, "Q").Value = "yes" Then
        
                Set xOutMail = xOutApp.CreateItem(0)
                xMailBody = "Dear " & Cells(r, "K").Value & vbNewLine & vbNewLine & _
                                              "I hope you are doing well today." & vbNewLine & vbNewLine & _
                                                "Please let us know if you have recieved any feedback from the customer regarding ticket  " & cd.Cells(r, "L").Value & "." & vbNewLine & _
                                                "Based on the client's feedback, we will determine possible next steps to ensure proper handling." & vbNewLine & vbNewLine & _
                                                cd.Cells(r, "G").Value & vbNewLine & vbNewLine & _
                                                "Thank you." & vbNewLine & vbNewLine & _
                                                "The Reefer Pricing Team"
                            
                On Error Resume Next
                With xOutMail
                .Display
                .SentonBehalfofName = "Name@Here.com"
                .Body = xMailBody & vbCrLf & .Body
                .To = cd.Cells(r, "H").Value
                .cc = "Name@Here.com"
                .Subject = "Quote Follow Up of Ticket " & cd.Cells(r, "L").Value
                               
                    
                 End With
                 On Error GoTo 0
                         
                End If
                
                
                Next
                
        For r = 2 To LastRow
                
                If cd.Cells(r, "S").Value = "yes" Then
        
                Set xOutMail = xOutApp.CreateItem(0)
                xMailBody = "Dear " & Cells(r, "K").Value & vbNewLine & vbNewLine & _
                                              "I hope you are doing well today." & vbNewLine & vbNewLine & _
                                                "A kind reminder to let us know if you recieved any feedback from the customer regarding ticket  " & cd.Cells(r, "L").Value & "." & vbNewLine & _
                                                "Timely Feedback, either postive or negative, will instruct us to file rates or provide guidance on how to adjust the quote on the next request." & vbNewLine & vbNewLine & _
                                                cd.Cells(r, "G").Value & vbNewLine & vbNewLine & _
                                                "Thank you." & vbNewLine & vbNewLine & _
                                                "The Reefer Pricing Team"
                            
                On Error Resume Next
                With xOutMail
                .Display
                .SentonBehalfofName = "Name@Here.com"
                .Body = xMailBody & vbCrLf & .Body
                .To = cd.Cells(r, "H").Value
                .cc = "Name@Here.com"
                .Subject = "Reminder Ticket " & cd.Cells(r, "L").Value
                               
                    
                 End With
                 On Error GoTo 0
                         
                End If
                
                
                Next
 
                
                
                
    Set xOutMail = Nothing
    Set xOutApp = Nothing
    
End Sub

 

[cokeoverpepsi123]

Anything here? Is there a better way to achieve what I am trying to do or am I totally out there?