VBA paste from clipboard to email

[best1]

Guys

having a real :banghead: moment here!

i have the code working below for sending out mass emails to a variety of cell value email addrsses, but i cant work out for the life of me how to paste from the clipboard instead of picking up the body text from a textbox on the userform... i know this is really stupid, but i am new to this game and would gratefully receive any help you might have!

here is code below - can i just replace the "& txtBodyText.Value" with a paste from clipboard instruction?!??!:


On Error Resume Next
With OutMail
.To = cell.Value
.Cc = txtCc.Value
.Bcc = txtBcc.Value
.Subject = txtSubject.Value
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & txtBodyText.Value



.Attachments.Add attach1
.Attachments.Add attach2
.Attachments.Add attach3
.Attachments.Add attach4
.Attachments.Add attach5
.Attachments.Add attach6

If chbDisplayEmailOnly.Value = True Then
If MsgBox("You are about to create mass emails for the recipients listed. They will be displayed, not sent. Do you want to continue?", vbYesNo, _
"My Title") = vbNo Then Exit Sub
.Display
Else
If MsgBox("You are about to create and send mass emails for the recipients listed. Do you want to continue?", vbYesNo, _
"My Title") = vbNo Then Exit Sub
.Send
End If

Thanks to everyone!

best1

[mdmackillop]

Check GetFromClipboard in VBA Help

I think you need something like
Dim MyData As DataObject
Set MyData = New DataObject
MyData.GetFromClipboard

'and
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & MyData.GetText(1)

[best1]

MD

many thanks for that - my hope was that by bringing in the data from clipbrd that i would be able to keep it in HTML...is this possible? essentially i want to be able to keep the formatting and footer etc of emails, without having to edit the code each time i use the system.

any further input you might be able to provide would be gratefully appreciated!

best1

[mdmackillop]

Sorry best, HTML is outwith my scope.

[best1]

thanks MD

the code i was using before worked occasionally, but the pause meant that it took forever when sending out mass mailings and it also kept tripping over itself and failing to send all of the emails etc.

is there some way to marry the two?


Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double
For r = 2 To 4 'data in rows 2-4
Email = Cells(r, 2)

Subj = "A topic"

Msg = ""
Msg = Msg & "Dear " & Cells(r, 1) & "," & vbCrLf & vbCrLf
Subj = Application.WorksheetFunction.Substitute(Subj, " ", "%20")
Msg = Application.WorksheetFunction.Substitute(Msg, " ", "%20")

Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, "%0D%0A")
URL = "mailto:" & Email & "?subject=" & Subj & "&body=" & Msg

ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
Application.Wait (Now + TimeValue("0:00:02"))
Application.SendKeys "{Tab}{Tab}{Tab}{Tab}{Tab}^{End}{Return}{Return}^v"
Application.SendKeys "%s"
Next r
End Sub


i look forward to hearing from anyone who can help...thanks guys!

[mdmackillop]

Hi Best
When you post code, please select it and click the VBA button. This formats it as shown above, making it more readable.
Regards
MD