Merging Macros

[rplx10]

Hi All,

I'm new to the site and to VBA. I was wondering if anyone could assist with a macro merge. I have two working macros (one I would like to tweak) but I am unable to make them work together.

I would appreciate any assistance that you can provide.

Thank you.

1st Macro used to generate multiple emails and populate fields and place attachments in email.

Sub SendingSheet()

Dim oMSOutlook As Object
Dim oEmail As Object
Dim x As Integer
x = 2

Do While IsEmpty(ActiveSheet.Cells(x, 1)) = False

Set oMSOutlook = CreateObject("Outlook.Application")
Set oEmail = oMSOutlook.CreateItem(olMailItem)



With oEmail



.To = ActiveSheet.Cells(x, 1)
.CC = ActiveSheet.Cells(x, 2)
.BCC = ActiveSheet.Cells(x, 3)
.Subject = ActiveSheet.Cells(x, 4)
'.Body = ActiveSheet.Cells(x, 5)
.Attachments.Add ActiveSheet.Cells(x, 6).Value

x = x + 1



.Display


End With

Loop


Set oMSOutlook = Nothing
Set oEmail = Nothing

End Sub

2nd Macro used to Insert Body into email using .doc file.

I would like to tweak this one...so that formatting is retained when being brought over to Outlook (spacing, hyperlinks, etc.) and providing a source path within the excel sheet (ex. Field 2,5) that contains path info for the .doc file containing the body to be used per outgoing email.

Sub SendOutlookMessages()


Dim OL As Object, MailSendItem As Object
Dim W As Object
Dim MsgTxt As String, SendFile As String
Dim ToRangeCounter As Variant


SendFile = Application.GetOpenFilename(Title:="Select MS Word " & _
"file to mail, then click 'Open'", buttontext:="Send", _
MultiSelect:=False)


Set W = GetObject(SendFile)


MsgTxt = W.Range(Start:=W.Paragraphs(1).Range.Start, _
End:=W.Paragraphs(W.Paragraphs.Count).Range.End)


Set W = Nothing


Set OL = CreateObject("Outlook.Application")
Set MailSendItem = OL.CreateItem(olMailItem)

ToRangeCounter = 0





With MailSendItem

.Body = MsgTxt


.Display
End With


Set OL = Nothing

End Sub

My Purpose is to create multiple emails that are to be either displayed or placed directly within Draft box (.Save) to be checked out for mistakes before leaving to desired recipients.

[Paul_Hossler]

1. You might get better results by asking in the Outlook forum, since it seems to be primarily an Outlook question. There are a lot of sharp Outlook-ers over there.

2. If you click the little [vba] icon just above the message writting area, you can paste your code between the two tags for nicer formatting:

[vba]
Sub SendingSheet()

Dim oMSOutlook As Object
Dim oEmail As Object
Dim x As Integer
x = 2

Do While IsEmpty(ActiveSheet.Cells(x, 1)) = False

Set oMSOutlook = CreateObject("Outlook.Application")
Set oEmail = oMSOutlook.CreateItem(olMailItem)

With oEmail

.To = ActiveSheet.Cells(x, 1)
.CC = ActiveSheet.Cells(x, 2)
.BCC = ActiveSheet.Cells(x, 3)
.Subject = ActiveSheet.Cells(x, 4)
'.Body = ActiveSheet.Cells(x, 5)
.Attachments.Add ActiveSheet.Cells(x, 6).Value

x = x + 1

.Display

End With

Loop

Set oMSOutlook = Nothing
Set oEmail = Nothing

End Sub

[/vba]

Paul

[rplx10]

Done.

[snb]

or:

[VBA]Sub M_snb()
with createobject("Outlook.Application")
for each cl in activesheet.columns(1).specialcells(2)
with .CreateItem(0)
.To = cl.value
.CC = cl.offset(,1).value
.BCC = cl.offset(,2).Value
.Subject = cl.offset(,3).Value
.Attachments.Add cl.offset(,5).Value
.Send
End With
Next
End with
End Sub[/VBA]

[rplx10]

Thank you for your response.

I tried the code provided but I think I should have included a Sample of the Spreadsheet so you could see exactly what I'm trying doing vs. my poor description.

I have uploaded an attachment so can see what happens when I run your code. For some reason it is not working for me.

[snb]

As you may see it's not a very complicated macro.
Analyse it and adapt it to your wishes.

[rplx10]

What about...

2nd Macro used to Insert Body into email using .doc file.

I would like to tweak this one...so that formatting is retained when being brought over to Outlook (spacing, hyperlinks, etc.) and providing a source path within the excel sheet (ex. Field 2,5) that contains path info for the .doc file containing the body to be used per outgoing email.

Could you assist with adding that functionality some place within your code?

Appreciate the assistance.

[snb]

Forget about retaining formatting in the body text.
The best way to do this is attaching the Word document as an attachment to the email.

[rplx10]

I wish this was an option, it would make my life a thousand times easier....unfortunately each email that is created must have a body, @ the moment the only lazy work around that I can come up with is to have the Body pasted in Outlook Signatures and applying to all new mail items. This works but @ times different emails require different Bodies to be linked. It would be much easier for me to insert path info within Excel to facilitate this function if possible.

Again thank you for all your assistance.