Joining 2 modules as 1 for email with Excel

L

losttoon

Hi, just like my nick I am lost. I need help with Excel in joining 2
modules as 1 for sending out 2 types of email templates to the
recipients. Can anyone help me? I have insert in the 2 modules that I
would like to form as 1.

Ron de Bruin, you would be finding the module familiar as I had almost
copied it all off from your website :) Thanks for the help you had
rendered in the past by sharing with us useful modules in your Excel
website. I really like your work alot. Keep it up

(Module 1)
Sub TestFile_2()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

On Error GoTo cleanup
For Each cell In Sheets("Sheet1").Columns("C").Cells.SpecialCells
(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0,
1).Value) = "reject" _
And LCase(cell.Offset(0, 2).Value) <> "send" Then
Set OutMail = OutApp.CreateItem(olMailItem)

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Thank You"
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine
& vbNewLine & _
"I am sorry you are not liable to resit for
your exam."
"Yours Sincerely," & vbNewLine & vbNewLine &
_
"School Administrator"
.Send 'Or use Display
End With

On Error GoTo 0

cell.Offset(0, 2).Value = "send"
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub

(Module 2)

Sub testfile_3()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range


Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon

On Error GoTo cleanup
For Each cell In Sheets("Sheet1").Columns
("C").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0,
4).Value) = "Pending"
And LCase(cell.Offset(0, 5).Value) <> "send" Then
Set OutMail = OutApp.CreateItem(olMailItem)

On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Thank You"
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine
& vbNewLine & _
"Your application is currently being
reconsidered." & cell.Offset(0, 1).Value & _
"Kindly refer to the blackboard on 31 January
for the result outcome." & vbNewLine & vbNewLine & _
"Yours Sincerely," & vbNewLine & vbNewLine & _
"School Administrator"
.Send 'Or use Display
End With

On Error GoTo 0

cell.Offset(0, 5).Value = "send"
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True

End Sub
 
S

Simon Lloyd

I don't understand why you want to join the 2 modules?, if you mean RU
the 2 modules you simply CALL the next module before the end of you
first code, so the very last bit of your first code would look lik
this

Code
-------------------
Application.ScreenUpdating = Tru
Call testfile_
End Su
-------------------

losttoon;183153 said:
Hi, just like my nick I am lost. I need help with Excel in joining
modules as 1 for sending out 2 types of email templates to th
recipients. Can anyone help me? I have insert in the 2 modules that
would like to form as 1

Ron de Bruin, you would be finding the module familiar as I had almos
copied it all off from your website :) Thanks for the help you ha
rendered in the past by sharing with us useful modules in your Exce
website. I really like your work alot. Keep it u
Code
-------------------
(Module 1
Sub TestFile_2(
Dim OutApp As Outlook.Applicatio
Dim OutMail As Outlook.MailIte
Dim cell As Rang

Application.ScreenUpdating = Fals
Set OutApp = CreateObject("Outlook.Application"
OutApp.Session.Logo

On Error GoTo cleanu
For Each cell In Sheets("Sheet1").Columns("C").Cells.SpecialCell
(xlCellTypeConstants
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0
1).Value) = "reject"
And LCase(cell.Offset(0, 2).Value) <> "send" The
Set OutMail = OutApp.CreateItem(olMailItem

On Error Resume Nex
With OutMai
.To = cell.Valu
.Subject = "Thank You
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLin
& vbNewLine &
"I am sorry you are not liable to resit fo
your exam.
"Yours Sincerely," & vbNewLine & vbNewLine

"School Administrator
.Send 'Or use Displa
End Wit

On Error GoTo

cell.Offset(0, 2).Value = "send
Set OutMail = Nothin
End I
Next cel

cleanup
Set OutApp = Nothin
Application.ScreenUpdating = Tru
End Su

(Module 2

Sub testfile_3(
Dim OutApp As Outlook.Applicatio
Dim OutMail As Outlook.MailIte
Dim cell As Rang


Application.ScreenUpdating = Fals
Set OutApp = CreateObject("Outlook.Application"
OutApp.Session.Logo

On Error GoTo cleanu
For Each cell In Sheets("Sheet1").Column
("C").Cells.SpecialCells(xlCellTypeConstants
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0
4).Value) = "Pending
And LCase(cell.Offset(0, 5).Value) <> "send" The
Set OutMail = OutApp.CreateItem(olMailItem

On Error Resume Nex
With OutMai
.To = cell.Valu
.Subject = "Thank You
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLin
& vbNewLine &
"Your application is currently bein
reconsidered." & cell.Offset(0, 1).Value &
"Kindly refer to the blackboard on 31 Januar
for the result outcome." & vbNewLine & vbNewLine &
"Yours Sincerely," & vbNewLine & vbNewLine &
"School Administrator
.Send 'Or use Displa
End Wit

On Error GoTo

cell.Offset(0, 5).Value = "send
Set OutMail = Nothin
End I
Next cel

cleanup
Set OutApp = Nothin
Application.ScreenUpdating = Tru

End Su
-------------------
Click to expand...

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com
 
L

losttoon

I don't understand why you want to join the 2 modules?, if you mean RUN
the 2 modules you simply CALL the next module before the end of your
first code, so the very last bit of your first code would look like
this:

Code:
--------------------
    Application.ScreenUpdating = True
  Call testfile_3
  End Sub
--------------------

losttoon;183153 Wrote:> Hi, just like my nick I am lost. I need help withExcel in joining 2


Code:
--------------------
  >   >  
  > (Module 1)
  > Sub TestFile_2()
  > Dim OutApp As Outlook.Application
  > Dim OutMail As Outlook.MailItem
  > Dim cell As Range
  >
  > Application.ScreenUpdating = False
  > Set OutApp = CreateObject("Outlook.Application")
  > OutApp.Session.Logon
  >
  > On Error GoTo cleanup
  > For Each cell In Sheets("Sheet1").Columns("C").Cells.SpecialCells
  > (xlCellTypeConstants)
  > If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0,
  > 1).Value) = "reject" _
  > And LCase(cell.Offset(0, 2).Value) <> "send" Then
  > Set OutMail = OutApp.CreateItem(olMailItem)
  >
  > On Error Resume Next
  > With OutMail
  > .To = cell.Value
  > .Subject = "Thank You"
  > .Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine
  > & vbNewLine & _
  > "I am sorry you are not liable to resit for
  > your exam."
  > "Yours Sincerely," & vbNewLine & vbNewLine &
  > _
  > "School Administrator"
  > .Send 'Or use Display
  > End With
  >
  > On Error GoTo 0
  >
  > cell.Offset(0, 2).Value = "send"
  > Set OutMail = Nothing
  > End If
  > Next cell
  >
  > cleanup:
  > Set OutApp = Nothing
  > Application.ScreenUpdating = True
  > End Sub
  >
  > (Module 2)
  >
  > Sub testfile_3()
  > Dim OutApp As Outlook.Application
  > Dim OutMail As Outlook.MailItem
  > Dim cell As Range
  >
  >
  > Application.ScreenUpdating = False
  > Set OutApp = CreateObject("Outlook.Application")
  > OutApp.Session.Logon
  >
  > On Error GoTo cleanup
  > For Each cell In Sheets("Sheet1").Columns
  > ("C").Cells.SpecialCells(xlCellTypeConstants)
  > If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0,
  > 4).Value) = "Pending"
  > And LCase(cell.Offset(0, 5).Value) <> "send" Then
  > Set OutMail = OutApp.CreateItem(olMailItem)
  >
  > On Error Resume Next
  > With OutMail
  > .To = cell.Value
  > .Subject = "Thank You"
  > .Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine
  > & vbNewLine & _
  > "Your application is currently being
  > reconsidered." & cell.Offset(0, 1).Value & _
  > "Kindly refer to the blackboard on 31 January
  > for the result outcome." & vbNewLine & vbNewLine & _
  > "Yours Sincerely," & vbNewLine & vbNewLine & _
  > "School Administrator"
  > .Send 'Or use Display
  > End With
  >
  > On Error GoTo 0
  >
  > cell.Offset(0, 5).Value = "send"
  > Set OutMail = Nothing
  > End If
  > Next cell
  >
  > cleanup:
  > Set OutApp = Nothing
  > Application.ScreenUpdating = True
  >
  > End Sub
  >
--------------------



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
Click to expand...

Dear Simon,

I had tried but there was no reaction when i tried to run the module.
Both modules are in the same excel file and i am trying to make the
system understand that when i key in reject, it will auto generate a
rejection letter to the applicant and if i enter KIV, it will auto
generate another KIV letter to the applicant.
I hope you will be able to assist in this as I have not much
experience in self creation of marco script and will need your support
in the script creation. Thanks for your help :)
 
S

Simon Lloyd

Why not join our forum for free where you can attach a workbook to you
post and we can help you directly with that?, if you do join make sur
you post in this thread so that people who have been helping you o
following the thread can still do so

losttoon;184784 said:
On Jan 15, 5:23*pm, Simon Lloyd <[email protected]
wrote



Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1)
Code Cage Forums'
(http://www.thecodecage.com/forumz/showthread.php?t=50607)

Dear Simon,

I had tried but there was no reaction when i tried to run the module.
Both modules are in the same excel file and i am trying to make the
system understand that when i key in reject, it will auto generate a
rejection letter to the applicant and if i enter KIV, it will auto
generate another KIV letter to the applicant.
I hope you will be able to assist in this as I have not much
experience in self creation of marco script and will need your support
in the script creation. Thanks for your help :)
Click to expand...


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

active row copy into Outlook 0
Sending e-mails from specific email address 2
Mail a different file(s) to each person in a range 0
mailing from excel - For each cell - already in use - error 3
Mail Multiple Sheets via PDF Q 5
Converting an Excel file to PDF Q 1
Copy select cells from active row into email body 0
Trigger Email with Excel File Closed 2

Top