VBA Send Keys to attach file in Gmail

[Johndinho]

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.

<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

 

[Norie]

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]

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..

.
.
.

        .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]

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.

        .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.

 

[Johndinho]

Thanks Norie!!