Sending an email to the name someone enters in a userform

Hi all,

I have created a ticketing system/issue logger with a userform. Users enter in the information on the Userform, and the data from their entry is logged on a single line in the spreadsheet. This is all working fine.

Userform Interface:
Userform Issue Logger.JPG

One of the fields is the "Submitted By" textbox. I would like them to enter their name, and have an email be sent to them with a summary of the issue they just logged. However, I want the "Submitted By" field to be filled with their name, not their email address. So that adds in another step. The name will go on the issue log spreadsheet, while the email address is used for the email summary.

My question is: Can I take the name they enter, find their corresponding email address, and then send them an email with a summary of the entered userform information?

My team isn't that big, so I would be able to have a hidden sheet with names --> email addresses. Maybe a VLOOKUP is what I am looking for?

It doesn't matter if they send themselves an Outlook email, or if I send them an Outlook email from my IT account.
Body of the email or attachment, whatever is easiest.

Kristen

Hey John,

I appreciate the welcome and help! This is my first ever experience in coding so I apologize if it is messy. Here is the full Excel book with fake/no data.

Beta Issue Log - Mockup Data.xlsm

As for your question, I am flexible about what is sent to the user. A line item would probably be easier (but again I am just guessing).

Thanks again!

Kristen

Hmm, right now there is a separate email button, but now that I think of it, I would prefer that button to not be there and instead an email to be sent once "SUBMIT" is hit.

In the popup that says, "Your entry has been successfully logged!", I will most likely modify it to include, "An email has been sent to your account with a ticket number and summary." (or something of that nature)

The email subject ideally would include the entered <Issue Title>, but again, I'm flexible and just brainstorming!

Oh gosh. Well right now it isn't, but we were hoping to have it in a shared folder that only our group of ~15 has access to. The current procedure is that I log everything manually myself. Having the file out and accessible was management's attempt to increase transparency.

Shoot me down though if this is a bad idea!!

Darn. I understand.

Would you recommend just scrapping it? I was excited about it, but obviously its ability to function comes before anything else.

If I make it read-only, then use the userform entry format myself to log and send emails, they could simply view the file to see the status of their issue. It's kinda gimmicky if I'm the only one using it, but we'll see.

Thoughts?

John,

Thanks again for all your help in this, you've been awesome.

Kristen

First I want to say thank you so much! I appreciate you taking the time to write that (and format what horrible code I had so far).

A quick question: Sometimes when copying the userform into an email, it gets cut off/cropped too short (pictured below). How do you change the dimensions of the screenshot? The userform appears in full when first pulled on the new sheet, between steps.

UF Screenshot.JPG

Sorry to bug you again. Hope all is well with you.

Kristen

John,

My mistake with wording -- It does indeed happen every time. I have added in new fields on the userform interface since I first sent it over to you. So while before it wasn't getting cut off, now it is.

Hey John,

Well I'm back. I considered starting a new thread but figured you're already familiar with the program.

I am having problems with one section in Module1. I receive the error "PasteSpecial method of Worksheet class failed" and then the section below highlights.

Please Login or Register  to view this content.

A blank Excel sheet displays with nothing on it.

Here it is in the context (full module):

Please Login or Register  to view this content.

Ideas?

Here it is in full. SME LOG - Fake Data.xlsm You can see right now it is kinda messy.

I have not been able to recreate the error so I am lost as what to do/how to fix it. It has only happened one time that I know of.

Thanks again.