Help: Ability to email, set appointments and send reminders

[mattm11]

Hello, this is my first post (I will be following up this post with another). I am Training & Development professional. This project is to design a peer-to-peer training tool in Excel. This will be available on the company's intranet, and will be used by all full time employees. There are 6 store locations.

The goal of the project is to encourage company wide collaboration; encourage training through peer pressure; encourage training through a channel that eliminates the need of an employee to ask "where can I get training on this?".
The problem is employees do not seek training because as of right now it is from management.

Details:
Excel 2013
Windows 8.1
Microsoft Outlook is the email server

Things to note: I am not very experienced in Excel; I am not experienced with VBA, macros. I can create scripts that import info from tabs into the main tab. I can create drop down lists with these scripts so that other cells are filled in with the related information.

Question:
I need each employee to use this spreadsheet...select their name, then select the topic they would like to be trained on. I have set it up so that when the user selects the topic, the name of the instructor is automatically populated.
? How can I set it up so that the user can press the cell next to the instructor's name and the "click" will bring up an [already populated] email [that would populate the instructor's email address in the "To:" field]...? Everything in the [would be ideal] but not necessary.
I think that this feature would allow the employee seeking training to be in full control. The email could even already be populated with the following: "Can you teach me [insert the subject] within the next two weeks? Please email me with your availability."

Per the title of this post I am also looking to see if its possible to do the same with making an appointment in their Outlook Calendar.
" " " " if its possible to do the same with sending a reminder to the instructor [and to the user].

Thank you for your time and help. I will do my best to provide any additional information.

Thanks!

 

[mattm11]

Just in case this is helpful, this is the script that creates the auto add of the instructor next to the subject:
=IF([@COURSE]="","",IFERROR(VLOOKUP([@COURSE],tblCourseList,2,0),"Not Found"))

 

[mattm11]

Here is my Box link to show an example
https://app.box.com/s/z4anjv46cue2f1pmh6gv

 

[mattm11]

I am using the following. I am keeping I as .Display but I still need a button in the Email column that if it can be automatically filled by the fact that the previous cells were filled out by the user…so again the user selects their own name, then selects the course that they would like to take, the script that I provided will fill in the instructors name, and then THE GOAL is to have the ability for the user to be able to click on the next cell a button that changes per the instructor’s name (which is filled automatically per the course selected)…this button will populate an email…with the instructor’s email address filled, the subject line filled with Teach Me- [subject], and the body saying the blurp about with you teach me this subject within the next two weeks.

  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0
     
    ' Prepare e-mail
    .Subject = Title
    .To = "..." ' <-- Put email of the recipient here
    .CC = "..." ' <-- Put email of 'copy to' recipient here
    .Body = "Hi," & vbLf & vbLf _
          & "Will you Teach Me [insert subject here]? What is your availability within the next two weeks? My name is [insert your name here], and my email address is [insert your email address]" & vbLf & vbLf _
          & "Many thanks," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Display
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent", vbExclamation
    Else
      MsgBox "E-mail successfully sent", vbInformation
    End If
    On Error GoTo 0
   
  End With
 
   ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub

 

[mattm11]

I get an error with that script though.

 

[mattm11]

Actually this is working for emailing...but my question remains...how do I create a button so that the user can initiate this macro for the email. And would it be possible that the email "to" field is already filled per the instructor that was automatically entered per the user's choice of the subject? (refer to the Box link above for an example layout of my cells).

Also, the next column should have another button as a reminder email.

Possible?

 

[mattm11]

I am using the following Macro with a Button. I have two problems...one is that when the button is pressed two Excel windows open at along the task bar - how can I eliminate that? Also, going back to my original question...is there a way that I can get the macro to take in the instructor's name into the "To:" field of the email? This will eliminate the need for the student to have to look up or know the instructor's email address. Lastly, in the body of the email I would love for the macro to extract the subject that the student chose in that row; I want to try to eliminate the use of [insert here].

 Sub EmailfromExcel()
  Dim IsCreated As Boolean
  Dim i As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object

  ' Not sure for what the Title is
  Title = Range("C2")

  ' Define PDF filename
  PdfFile = ActiveWorkbook.FullName
  i = InStrRev(PdfFile, ".")
  If i > 1 Then PdfFile = Left(PdfFile, i - 1)
  PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"

  ' Export activesheet as PDF
  With ActiveSheet
    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
  End With

  ' Use already open Outlook if possible
  On Error Resume Next
  Set OutlApp = GetObject(, "Outlook.Application")
  If Err Then
    Set OutlApp = CreateObject("Outlook.Application")
    IsCreated = True
  End If
  OutlApp.Visible = True
  On Error GoTo 0

  ' Prepare e-mail with PDF attachment
  With OutlApp.CreateItem(0)

    ' Prepare e-mail
    .Subject = Title
    .To = ""  ' <-- Put email of the recipient here
    .CC = "mmcmullen@landrys.com" ' <-- Put email of 'copy to' recipient here
    .Body = "Hi," & vbLf & vbLf _
          & "Will you 'Teach Me' [insert subject here] within the next 2 weeks? Please email me your availability and I will be happy to adjust my schedule to meet yours." & vbLf & vbLf _
          & "Regards," & vbLf _
          & "[insert your name & store here]" & vbLf & vbLf
    .Attachments.Add PdfFile

    ' Try to send
    On Error Resume Next
    .Display
    Application.Visible = True
    If Err Then
      MsgBox "Please make sure you are signed in to your Landry's Outlook email account. Then please go back and press the Email Instructor button again", vbExclamation
    Else
      MsgBox "Please fill in the [insert here] areas of the email", vbInformation
    End If
    On Error GoTo 0

  End With

  ' Delete PDF file
  Kill PdfFile

  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit

  ' Release the memory of object variable
  Set OutlApp = Nothing

 End Sub

 

[mattm11]

If I made this too confusing, or if you need further/different information, please let me know! I really would love this to work. Please help.

 

[mattm11]

Please help. I will be happy to re-explain.