Yes, it is possible.

You'll have to create a custom component that references the custom controller. Then, place that custom controller within the <messaging:attachment> tags of your Visualforce email template.

For example, if you have the following custom controller that finds accounts named Smith:

public class findSmithAccounts {
	private final List<Account> accounts;

	public findSmithAccounts() {
		accounts = [select Name from Account where Name LIKE 'Smith_%'];
	}

	public List<Account> getSmithAccounts() {
		return accounts;
	}
}

 You would refer to this in a custom component named smithAccounts (make sure it is access global):

<apex:component controller="findSmithAccounts" access="global">
	<apex:dataTable value="{!SmithAccounts}" var="s_account">
		<apex:column>
			<apex:facet name="header">Account Name</apex:facet>
			{!s_account.Name}
		</apex:column>
	</apex:dataTable>
</apex:component>

Then, within your Visualforce email template, you would call the custom controller within <messaging:attachment>, making sure to render it as PDF:

<messaging:emailTemplate subject="Embedding Custom Controllers" recipientType="Contact" relatedToType="Account">
    <messaging:htmlEmailBody >
        <p>As you requested, here's a list of all our Smith accounts:</p>
        <p>Hope this helps !</p>
    </messaging:htmlEmailBody>

    <messaging:attachment renderAs="pdf">
        <c:smithAccounts />
    </messaging:attachment>

</messaging:emailTemplate>