Needs Help With InputBox for Sending Emails from Gmail

Noah
Noah used Ask the Experts™
on
Greetings experts,

Here is my code:
Option Explicit
Public Sub LoopCells()
 
Dim c As Range
 
For Each c In Range("G2:G1048576")
    If c.Value <> "" And c.Value < 4 Then
     Call SendGmail
    End If
Next c
 
End Sub

Function SendGmail()

   'creating a CDO object
   Dim Mail As CDO.Message
   Set Mail = New CDO.Message

   'Enable SSL Authentication
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

   'Make SMTP authentication Enabled=true (1)
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

   'Set the SMTP server and port Details
   'Get these details from the Settings Page of your Gmail Account
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
   "smtp.gmail.com"
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

   'Set your credentials of your Gmail Account
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusername") = _
   ""
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = _
   ""

   'Update the configuration fields
   Mail.Configuration.Fields.Update

   'Set All Email Properties
   With Mail
      .Subject = "Update on transfusion product (EMERGENCY!!)"
      .From = ""
      .To = ""
      .CC = ""
      .BCC = ""
      .TextBody = "Product has reached a minimum value of " + c.Value
   End With
   'to send the mail
   Mail.Send

End Function

Open in new window


This code will send an email for each cell in column G which has a value less than 4.

This part doesn't work. I am trying to include the value in the cell into the email body. Right now, it says object undefined.
  .TextBody = "Product has reached a minimum value of " + c.Value

Open in new window


I also need help making an InputBox which will ask for the user's email and password and the recipient's email. This InputBox should only need to appear once for every email that needs to be sent. I am trying to make the InputBox replace the respective parts of the code below every time the code is run.

'Set your credentials of your Gmail Account
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusername") = _
   ""
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = _
   ""

Open in new window


 With Mail
      .Subject = "Update on transfusion product (EMERGENCY!!)"
      .From = ""
      .To = ""
      .CC = ""
      .BCC = ""
      .TextBody = "Product has reached a minimum value of " + c.Value
   End With

Open in new window


Here is a sample file: Excel.xlsm

Any help is much appreciated! :)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
At first you are passing the Cell as Argument to the SendMail
The c is declared in LoopCells but SendMail knows nothing about it
Software & Systems Engineer
Commented:
It should something like this
Public Sub LoopCells()
 
Dim c As Range
 
For Each c In Range("G2:G1048576")
    If c.Value <> "" And c.Value < 4 Then
     SendGmail c
    End If
Next c
End Sub
Sub SendGmail(mcell As Object)
MsgBox mcell.Value
End Sub

Open in new window

John TsioumprisSoftware & Systems Engineer
Commented:
I am attaching a modified Excel
EDITED...i forgot something
Excel.xlsm
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

NoahHardware Tester and Debugger

Author

Commented:
@John Tsioumpris Hi Thanks for your reply! It says "Argument not optional". What should I do?

EDIT:Okay, will take a look at the new one!
NoahHardware Tester and Debugger

Author

Commented:
There is a type mismatch at this line. What should I do?
     .TextBody = "Product has reached a minimum value of " + lowCell.Value

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
wait to take a look...strange
John TsioumprisSoftware & Systems Engineer

Commented:
Replace + with &
This was the only line i didn't checked
NoahHardware Tester and Debugger

Author

Commented:
Alright, I think it's pretty much almost done! :) But is there a way to add error handlers? In case they press cancel, I don't want them to see the Visual Basic Editor. Maybe just a messagebox telling them to input the minimum fields needed for it to work and then stop everything properly.

Capture.PNG
John TsioumprisSoftware & Systems Engineer
Commented:
The easy fix is to add a On Error Resume Next
Function SendGmail(lowCell As Range)
On Error Resume Next

Open in new window

The real fix after you finished your tests is to rethink your logic
right now you have 1 entry that needs emailing...what if you had 10...or 100 or 1000 ...would you send 1000 emails
The correct way is to store all the info somewhere (maybe ask only for sender password) and to send a "concatenated" TextBody containing all the problematic entries.
I am not an Excel guy but you should make a small form where you would pull the necessary recepient/CC/BCC/username ...run the checking and if low qualities found ask for password and send the mail.
NoahHardware Tester and Debugger

Author

Commented:
I understand where you are coming from!

I realized an issue when I tried adding more requirements to my code. Now that I have three different procedures, is there a way to combine all the inputboxes?

Option Explicit
Public Sub LoopCells()
 
Dim c As Range
 
For Each c In Range("G2:G1048576")
    If c.Value <> "" And c.Value < 4 Then
      SendCriticalGmail c
    End If
Next c

For Each c In Range("G2:G1048576")
    If c.Value <> "" And c.Value > 6 Then
      SendNormalGmail c
    End If
Next c

For Each c In Range("G2:G1048576")
    If c.Value <> "" And c.Value < 7 And c.Value > 3 Then
      SendMinimumGmail c
    End If
Next c
 
End Sub

Function SendCriticalGmail(lowCell As Range)

On Error Resume Next
   'creating a CDO object
   Dim senderUserName As String
   Dim senderPassword As String
   Dim Mail As CDO.Message
   Set Mail = New CDO.Message

   'Enable SSL Authentication
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

   'Make SMTP authentication Enabled=true (1)
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

   'Set the SMTP server and port Details
   'Get these details from the Settings Page of your Gmail Account
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
   "smtp.gmail.com"
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    senderUserName = InputBox("Please Enter Sender's Gmail Address" & vbNewLine & "This field is compulsory! If you do not fill in all compulsory fields (correctly), the emails won't be sent")
    senderPassword = InputBox("Please Enter Sender's Gmail Password" & vbNewLine & "This field is compulsory! If you do not fill in all compulsory fields (correctly), the emails won't be sent")
   'Set your credentials of your Gmail Account
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusername") = senderUserName
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = senderPassword

   'Update the configuration fields
   Mail.Configuration.Fields.Update

   'Set All Email Properties
   With Mail
      .Subject = "Update on transfusion product (EMERGENCY!!)"
      .From = senderUserName
      .To = InputBox("Enter Recipient Email Address" & vbNewLine & "This field is compulsory! If you do not fill in all compulsory fields (correctly), the emails won't be sent" & vbNewLine & "If you need to send to more than one recipient in this field, just type ; followed by the next email.")
      .CC = InputBox("Enter CC Recipient Email Address" & vbNewLine & "This field is optional! If you do not need to type anything, just press ok" & vbNewLine & "If you need to send to more than one recipient in this field, just type ; followed by the next email.")
      .BCC = InputBox("Enter BC Recipient Email Address" & vbNewLine & "This field is optional! If you do not need to type anything, just press ok" & vbNewLine & "If you need to send to more than one recipient in this field, just type ; followed by the next email.")
      .TextBody = "Product has reached a critical value of " & lowCell.Value
   End With
   'to send the mail
   Mail.Send

End Function

Function SendNormalGmail(lowCell As Range)

On Error Resume Next
   'creating a CDO object
   Dim senderUserName As String
   Dim senderPassword As String
   Dim Mail As CDO.Message
   Set Mail = New CDO.Message

   'Enable SSL Authentication
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

   'Make SMTP authentication Enabled=true (1)
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

   'Set the SMTP server and port Details
   'Get these details from the Settings Page of your Gmail Account
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
   "smtp.gmail.com"
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    senderUserName = InputBox("Please Enter Sender's Gmail Address" & vbNewLine & "This field is compulsory! If you do not fill in all compulsory fields (correctly), the emails won't be sent")
    senderPassword = InputBox("Please Enter Sender's Gmail Password" & vbNewLine & "This field is compulsory! If you do not fill in all compulsory fields (correctly), the emails won't be sent")
   'Set your credentials of your Gmail Account
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusername") = senderUserName
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = senderPassword

   'Update the configuration fields
   Mail.Configuration.Fields.Update

   'Set All Email Properties
   With Mail
      .Subject = "Update on transfusion product (EMERGENCY!!)"
      .From = senderUserName
      .To = InputBox("Enter Recipient Email Address" & vbNewLine & "This field is compulsory! If you do not fill in all compulsory fields (correctly), the emails won't be sent" & vbNewLine & "If you need to send to more than one recipient in this field, just type ; followed by the next email.")
      .CC = InputBox("Enter CC Recipient Email Address" & vbNewLine & "This field is optional! If you do not need to type anything, just press ok" & vbNewLine & "If you need to send to more than one recipient in this field, just type ; followed by the next email.")
      .BCC = InputBox("Enter BC Recipient Email Address" & vbNewLine & "This field is optional! If you do not need to type anything, just press ok" & vbNewLine & "If you need to send to more than one recipient in this field, just type ; followed by the next email.")
      .TextBody = "Product has reached a normal value of " & lowCell.Value
   End With
   'to send the mail
   Mail.Send

End Function

Function SendMinimumGmail(lowCell As Range)

On Error Resume Next
   'creating a CDO object
   Dim senderUserName As String
   Dim senderPassword As String
   Dim Mail As CDO.Message
   Set Mail = New CDO.Message

   'Enable SSL Authentication
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

   'Make SMTP authentication Enabled=true (1)
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

   'Set the SMTP server and port Details
   'Get these details from the Settings Page of your Gmail Account
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
   "smtp.gmail.com"
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    senderUserName = InputBox("Please Enter Sender's Gmail Address" & vbNewLine & "This field is compulsory! If you do not fill in all compulsory fields (correctly), the emails won't be sent")
    senderPassword = InputBox("Please Enter Sender's Gmail Password" & vbNewLine & "This field is compulsory! If you do not fill in all compulsory fields (correctly), the emails won't be sent")
   'Set your credentials of your Gmail Account
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusername") = senderUserName
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = senderPassword

   'Update the configuration fields
   Mail.Configuration.Fields.Update

   'Set All Email Properties
   With Mail
      .Subject = "Update on transfusion product (EMERGENCY!!)"
      .From = senderUserName
      .To = InputBox("Enter Recipient Email Address" & vbNewLine & "This field is compulsory! If you do not fill in all compulsory fields (correctly), the emails won't be sent" & vbNewLine & "If you need to send to more than one recipient in this field, just type ; followed by the next email.")
      .CC = InputBox("Enter CC Recipient Email Address" & vbNewLine & "This field is optional! If you do not need to type anything, just press ok" & vbNewLine & "If you need to send to more than one recipient in this field, just type ; followed by the next email.")
      .BCC = InputBox("Enter BC Recipient Email Address" & vbNewLine & "This field is optional! If you do not need to type anything, just press ok" & vbNewLine & "If you need to send to more than one recipient in this field, just type ; followed by the next email.")
      .TextBody = "Product has reached a minimum value of " & lowCell.Value
   End With
   'to send the mail
   Mail.Send

End Function

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
I think its time to close this question and open another one for refactoring your code.
NoahHardware Tester and Debugger

Author

Commented:
Actually, this is part of my question.. "This InputBox should only need to appear once for every email that needs to be sent."
NoahHardware Tester and Debugger

Author

Commented:
I have marked those solutions which are for solving my problem :)
John TsioumprisSoftware & Systems Engineer

Commented:
Now that i have glanced your code ....you just need to provide one more argument to SendMail
Function SendCriticalGmail(lowCell As Range,levelOfImportance as Integer)

Open in new window

If c.Value <> "" And c.Value < 4 Then       SendCriticalGmail c ,1 
If c.Value <> "" And c.Value > 6 Then       SendCriticalGmail c,2
If c.Value <> "" And c.Value < 7 And c.Value > 3 Then       SendCriticalGmail c,3

Open in new window

1 -> Critical
2 -> Normal
3 -> Minimum

Select Case levelOfImportance 
case is =1
 .TextBody = "Product has reached a critical value of " & lowCell.Value
case is = 2
 .TextBody = "Product has reached a Normal value of " & lowCell.Value
case is = 3
 .TextBody = "Product has reached a Minmum value of " & lowCell.Value
Case else
 .TextBody = "Product has reached an Undefined value of " & lowCell.Value
End Select

Open in new window

NoahHardware Tester and Debugger

Author

Commented:
How should I put it... When it wants to send for Critical, it prompts all the Inputboxes. Then when it wants to send for Normal, it prompts all the InputBoxes again. The same goes for Minimum. How can I combine Critical, Normal and Minimum to use the same set of InputBoxes? This is what I meant by combining the InputBoxes.
NoahHardware Tester and Debugger

Author

Commented:
This is what I have for now.

Option Explicit
Public Sub LoopCells()
 
Dim c As Range
 
For Each c In Range("G2:G1048576")
    If c.Value <> "" And c.Value < 4 Then
      SendGmail c, 1
    End If
Next c

For Each c In Range("G2:G1048576")
    If c.Value <> "" And c.Value > 6 Then
      SendGmail c, 2
    End If
Next c

For Each c In Range("G2:G1048576")
    If c.Value <> "" And c.Value < 7 And c.Value > 3 Then
      SendGmail c, 3
    End If
Next c


End Sub

Function SendGmail(lowCell As Range, levelOfImportance As Integer)



On Error Resume Next
   'creating a CDO object
   Dim senderUserName As String
   Dim senderPassword As String
   Dim Mail As CDO.Message
   Set Mail = New CDO.Message

   'Enable SSL Authentication
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

   'Make SMTP authentication Enabled=true (1)
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

   'Set the SMTP server and port Details
   'Get these details from the Settings Page of your Gmail Account
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
   "smtp.gmail.com"
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    senderUserName = InputBox("Please Enter Sender's Gmail Address" & vbNewLine & "This field is compulsory! If you do not fill in all compulsory fields (correctly), the emails won't be sent")
    senderPassword = InputBox("Please Enter Sender's Gmail Password" & vbNewLine & "This field is compulsory! If you do not fill in all compulsory fields (correctly), the emails won't be sent")
   'Set your credentials of your Gmail Account
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusername") = senderUserName
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = senderPassword

   'Update the configuration fields
   Mail.Configuration.Fields.Update


   'Set All Email Properties
   With Mail
      .Subject = "Update on transfusion product (EMERGENCY!!)"
      .From = senderUserName
      .To = InputBox("Enter Recipient Email Address" & vbNewLine & "This field is compulsory! If you do not fill in all compulsory fields (correctly), the emails won't be sent" & vbNewLine & "If you need to send to more than one recipient in this field, just type ; followed by the next email.")
      .CC = InputBox("Enter CC Recipient Email Address" & vbNewLine & "This field is optional! If you do not need to type anything, just press ok" & vbNewLine & "If you need to send to more than one recipient in this field, just type ; followed by the next email.")
      .BCC = InputBox("Enter BC Recipient Email Address" & vbNewLine & "This field is optional! If you do not need to type anything, just press ok" & vbNewLine & "If you need to send to more than one recipient in this field, just type ; followed by the next email.")
      Select Case levelOfImportance
Case Is = 1
 .TextBody = "Product has reached a critical value of " & lowCell.Value
Case Is = 2
 .TextBody = "Product has reached a Normal value of " & lowCell.Value
Case Is = 3
 .TextBody = "Product has reached a Minmum value of " & lowCell.Value
Case Else
 .TextBody = "Product has reached an Undefined value of " & lowCell.Value
End Select
   End With
   'to send the mail
   Mail.Send

End Function

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
You will have  only 1 SendMail...the 2nd argument will take care of the Subject.
Also you need to move the Variables that hold the username,password...etc out of the functions so after they populated  you don't request them again...(you have to perform a check here)
John TsioumprisSoftware & Systems Engineer

Commented:
Lets make a quick change
Option Explicit
 Dim senderUserName As String
 Dim senderPassword As String
Dim recTo as String
Dim recCC as String
Dim recBC as string
Public Sub LoopCells()
 
Dim c As Range
 
For Each c In Range("G2:G1048576")
    If c.Value <> "" And c.Value < 4 Then       SendGmail c, 1
    If c.Value <> "" And c.Value > 6 Then      SendGmail c, 2
    If c.Value <> "" And c.Value < 7 And c.Value > 3 Then       SendGmail c, 3
Next c

End Sub

Function SendGmail(lowCell As Range, levelOfImportance As Integer)



On Error Resume Next
   'creating a CDO object
  
   Dim Mail As CDO.Message
   Set Mail = New CDO.Message

   'Enable SSL Authentication
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

   'Make SMTP authentication Enabled=true (1)
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

   'Set the SMTP server and port Details
   'Get these details from the Settings Page of your Gmail Account
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
   "smtp.gmail.com"
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
if Len(senderUserName ) =0 then
    senderUserName = InputBox("Please Enter Sender's Gmail Address" & vbNewLine & "This field is compulsory! If you do not fill in all compulsory fields (correctly), the emails won't be sent")
end if

    senderPassword = InputBox("Please Enter Sender's Gmail Password" & vbNewLine & "This field is compulsory! If you do not fill in all compulsory fields (correctly), the emails won't be sent")
   'Set your credentials of your Gmail Account
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusername") = senderUserName
   Mail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = senderPassword

   'Update the configuration fields
   Mail.Configuration.Fields.Update


   'Set All Email Properties
   With Mail
      .Subject = "Update on transfusion product (EMERGENCY!!)"
      .From = senderUserName
      .To = InputBox("Enter Recipient Email Address" & vbNewLine & "This field is compulsory! If you do not fill in all compulsory fields (correctly), the emails won't be sent" & vbNewLine & "If you need to send to more than one recipient in this field, just type ; followed by the next email.")
      .CC = InputBox("Enter CC Recipient Email Address" & vbNewLine & "This field is optional! If you do not need to type anything, just press ok" & vbNewLine & "If you need to send to more than one recipient in this field, just type ; followed by the next email.")
      .BCC = InputBox("Enter BC Recipient Email Address" & vbNewLine & "This field is optional! If you do not need to type anything, just press ok" & vbNewLine & "If you need to send to more than one recipient in this field, just type ; followed by the next email.")
      Select Case levelOfImportance
Case Is = 1
 .TextBody = "Product has reached a critical value of " & lowCell.Value
Case Is = 2
 .TextBody = "Product has reached a Normal value of " & lowCell.Value
Case Is = 3
 .TextBody = "Product has reached a Minmum value of " & lowCell.Value
Case Else
 .TextBody = "Product has reached an Undefined value of " & lowCell.Value
End Select
   End With
   'to send the mail
   Mail.Send

End Function

Open in new window

I only made one example ...you will work your way on the rest
if Len(senderUserName ) =0 then
    senderUserName = InputBox("Please Enter Sender's Gmail Address" & vbNewLine & "This field is compulsory! If you do not fill in all compulsory fields (correctly), the emails won't be sent")
end if

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial