VBA Send Keys to attach file in Gmail

Johndinho

Board Regular
Joined
Mar 21, 2013
Messages
85
Hi,

I have the code below triggered in an excel workbook to open gmail, attach a file and send it to the specified address. When I run the code; the attaching of the file doesn't work. It finds the correct file but wont click "Open" to attach it.

Code:
<johnpowell@effectphotonics.nl>Sub ActivateGmail()
    
    Handle = "C:\Program Files (x86)\Google\Chrome\Application\chrome.exe"
    RetVal = Shell(Handle, 1) '      Open
    Application.Wait Now + TimeValue("00:00:02")


    SendKeys ("https://mail.google.com/mail/u/0/#inbox?compose=new"), True
    SendKeys ("{ENTER}"), True
            Application.Wait Now + TimeValue("00:00:04")
    SendKeys ("C"), True                                                      'Open compose message popup
            Application.Wait Now + TimeValue("00:00:04")
    SendKeys ("<me@myaddress.com>"), True                      'Enter destination address
            Application.Wait Now + TimeValue("00:00:01")
    SendKeys ("{TAB}"), True
            Application.Wait Now + TimeValue("00:00:01")
    SendKeys ("{TAB}"), True
            Application.Wait Now + TimeValue("00:00:01")
    SendKeys ("Test"), True                                                    'Enter subject
            Application.Wait Now + TimeValue("00:00:01")
    
    
    'Get the Attachment
    SendKeys ("{TAB 3}"), True                                               'Move cursor to "AttachFile button
            Application.Wait Now + TimeValue("00:00:01")
            
    SendKeys ("{ENTER}"), True
            Application.Wait Now + TimeValue("00:00:01")
    
    SendKeys ("%{UP 10}"), True                                           'Move directory to Desktop (Where the file is saved)
            Application.Wait Now + TimeValue("00:00:01")
    
    
    
    SendKeys ("Filename"), True                                             'Specify the name of the file (I have tried this with and without the .xlsx extension)
            Application.Wait Now + TimeValue("00:00:01")
    SendKeys ("{ENTER}"), True                                             [B]'Any or all of the next 3 "Send Keys" should confirm the file selection and attach it to the email[/B]
            Application.Wait Now + TimeValue("00:00:01")          [B] 'I have tried all combinations of 1, 2 and all 3 but the command is not actioned!!![/B]
    SendKeys ("{ENTER}"), True
            Application.Wait Now + TimeValue("00:00:01")
    SendKeys ("%({O})"), True
            Application.Wait Now + TimeValue("00:00:01")
        
    'Send
    SendKeys (("^({ENTER})")), True                                        'Ctrl+Enter will send the mail
            Application.Wait Now + TimeValue("00:00:01")
    SendKeys ("%{F4}"), True                                                   'Close gmail
            Application.Wait Now + TimeValue("00:00:01")
    SendKeys ("{Enter}"), True                                                  'Leave site confirmaiton
            Application.Wait Now + TimeValue("00:00:01")
    SendKeys ("%{F4}"), True                                                    'Close Chrome
    SendKeys ("{NUMLOCK}"), True                                            'Correct the "SendKeys" NumLock bug


End Sub
</me@myaddress.com></johnpowell@effectphotonics.nl>

I don't have the option of using the CDO commands as the macro will be used by different people from a central location who have different passwords to gmail...........unless I can use a userform to prompt their password entry, feed that value into the code and then delete the data? Is that possible?

Any advice would be much appreciated. Especially with solving the SendKeys issue as this will allow me to complete several other ideas..

Thanks in advance for the brilliant ideas this forum always generates.

Johndinho
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Don't see why you couldn't use a userform for this, and you wouldn't need to delete the password because it wouldn't need to be save anywhere.

Do you have the code for doing this using CDO?

PS Using SendKeys is notoriously flaky and if this is going to be used by other people it's almost guaranteed to go wrong.
 

Johndinho

Board Regular
Joined
Mar 21, 2013
Messages
85
Thanks Norie,

I agree SendKeys is unreliable but to try and avoid users entering their password I was hoping this would work...

I'm new to userforms so my approach would be

User enters their gmail address and password
Take that information to A1 & A2
Then use something like..

Code:
.
.
.

        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = Range("A1").Value
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = Range("A2").Value
.
.
.
        .Send
.
.
.
        Range("A1:A2").ClearContents
.
.
.
        End Sub

Is that the best approach or do you know a better way?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
There would be no need to put the username/password in cells on the worksheet.

Let's say you have 2 textboxes, txtUserName and txtPassword for the username and password respectively, on the userform.

You could then use something like this in the code for a Send/Submit button on the form.
Code:
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = txtUserName.Value
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = txtPassword.Value

Note, you can set a textbox to display * to hide what's being typed using the PasswordChar property.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,842
Messages
5,598,394
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top