Buttons for Emailing, scheduling and sending reminders from Excel 2013

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

Hi,

This link is most useful for mailing thru excel:

http://www.rondebruin.nl/win/section1.htm

~

I suggest you review the forum rules, particularly relating to code tags and cross-posting.

Hi,

I was able to get the To email address(you'd have to write a vlook up formula for that) and capturing the subject line in the body of the email.

See the updated code below.

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 = [E2].Value  ' <-- 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 " & Title & " 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