I need to combine 2 VBA codes into one macro, please help

[GeeBDee]

Hi all

I have macros I wish to use/run in the same worksheet but I wish to combine them into 1. Please could anyone advise if this is possible and if so give a resolve? Codes Below.

In order:

Code 1

Sub Mail_workbook_1()

Dim wb As Workbook
Dim I As Long
Dim InputRange As Range

Set InputRange = Worksheets("Impact Study Proforma").Range("J3,J5,J6,J8,J14,J15")

'check all fields completed
If WorksheetFunction.CountA(InputRange) < 6 Then
MsgBox "Please Complete all fields marked with an Asterix", 48, "Missing info"
Exit Sub
End If

Set wb = ActiveWorkbook

On Error Resume Next
For I = 1 To 3
wb.SendMail "", "Request For Impact Study"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0
'clear input range
InputRange.ClearContents
End Sub


Code 2

Sub Mail_workbook_Outlook_1()
'Working in Excel 2000-2013
'This example send the last saved version of the Activeworkbook
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = ""
.CC = " GRANT.DALTON@thameswater.co.uk "
.BCC = ""
.Subject = "..Proforma For Impact Study"
.Body = ""
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End SubSub

Many thanks

Grant

 

[JustynaMK]

Hi Grant,

Why would you like to combine these two codes?

How about simply creating another macro:


Sub CombM()


Call Mail_workbook_1
Call Mail_workbook_Outlook_1


End Sub

 

[GeeBDee]

Hi Justyna

I was unaware that you can have multiple macros running in the same excel sheet? I have it in the back of my mind that I would have to combine them, but I can have multiples then I will do this.

Grant

 

[JustynaMK]

Yes you can (thankfully)! I hope it will work fine for you - let me know if you experience any issues.

Take care,
Justyna