email area using Yahoo

[rfletcher35]

Hi guys,

Hoping you can help me, I have a range of cells on a sheet A1:H32.
I want to automate copying this area as a bitmap and sending to multiple recipients using my yahoo email.

Does anyone have a bit of code for this please?

Thanks

Fletch

 

[Leith Ross]

Hello rfletcher35,

While it is easy to copy a range as a picture to the clipboard, it becomes difficult to incorporate it into multiple emails. It would be easier to copy the Range into the HTML body of the email. Unlike a bitmap, this does not require saving the picture as a local file and adding it on as an attachment.

 

[Kenneth Hobson]

If you are using Outlook and want to use the htmlBody method that Leith explained, see: https://www.rondebruin.nl/win/s1/outlook/bmail2.htm

Ron de Bruin has CDO examples as well.

There is also a WordEditor method for Outlook that one can use which is sort of a copy/paste thing. It takes more effort to learn this method.

 

[rfletcher35]

Hi,

Thanks for the reply. I don't mind how it is done really as long as that range of cells is sent so the 3 or 4 recipients can receive it as it is for a staff rota. I am ok doing it manually each week but I am passing this task on to someone else who isn't exactly tech savvy so wanted to simplify it by a push of a button and then it sends. I have managed to get hold of a bit of code but this is coming back with an error. I have attached the code I have so far, I have also tried changing the ports in the code but to no avail.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub CDO_Mail_Small_Text()
Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim Flds As Variant

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] iConf.Load -1
Set Flds = iConf.Fields

With Flds
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.yahoo.com" 'Change it according to the email server used
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25 ' Use 465 / 25 / 587
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "rfletcher35@yahoo.co.uk" ' Use the email id from which you want to send email
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "********" ' Type sender email account's password
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
.Update
End With[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] strbody = "Hello There," & vbNewLine & vbNewLine & _
"This is a test message from VBA Excel Mail sending Program. Thank you for using me." & vbNewLine & _
"Thank you" & vbNewLine & _
"Regards"[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] With iMsg
Set .Configuration = iConf
.To = "rfletcher35@yahoo.co.uk" ' Type email if of receipient
.From = "rfletcher35@yahoo.co.uk" ' Type sender email account again
.Subject = "Important message"
.TextBody = strbody
.Send
End With[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif] Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing
End Sub
'Hit the play button in Toolbar to test mail[/FONT]


This currently comes back with transport failed to connect to server message

 

[rfletcher35]

Hi

Just to add I have now added my yahoo account into my Outlook if this makes things easier?

Fletch

 

[Leith Ross]

Hello rfletcher35,

The wrong port number can result in transport failed to connect. The SMTP port number for Yahoo is 465.